Progression matrix

[Nick Horn]

Hi

I am using a large excel file, in both 2003 and 2007 versions. The data
contains exam results based on exams 6 years apart and looks like this.

KS2 Grade KS4 Grade
4 6
5 8
3 2
5 5
5 5

I need to produce a matrix which looks like this:

KS4 1 2 3 4 5 6 7 8
KS2
5 2 1
4 1
3 1
2

Any help is much appreciated with many thanks in anticipation.

Best wishes

Nick

 

[Herbert Seidenberg]

Excel 2007
Pivot Table
No formulas used:
http://www.mediafire.com/file/wmzz1wy2qdc/12_08_08.xlsx

 

[Pete_UK]

Nick,

I've assumed that your data is in Sheet1, occupying A2 to B6, and that
you want a summary table in a different sheet (Sheet2), laid out so
that your headers are in B1:J1 and A2:A6. Put this formula in C3:

=SUMPRODUCT((Sheet1!$A$2:$A$6=$A3)*(Sheet1!$B$2:$B$6=C$1))

and copy across and down to occupy C3:J6. Of course, your real data
will occupy more than 6 rows in Sheet1, so adjust the ranges
accordingly.

If you want to avoid showing a lot of zeros, apply conditional
formatting to C3:J6 such that if Cell Value is 0 (zero), then set the
foreground colour to white.

Another way would be to use a Pivot Table, but then I'm a formula
man !!.

Hope this helps.

Pete

 

[Nick Horn]

Many thanks.

Regards

Nick

 

[Nick Horn]

Many thanks

Regards

Nick

Nick,

I've assumed that your data is in Sheet1, occupying A2 to B6, and that
you want a summary table in a different sheet (Sheet2), laid out so
that your headers are in B1:J1 and A2:A6. Put this formula in C3:

=SUMPRODUCT((Sheet1!$A$2:$A$6=$A3)*(Sheet1!$B$2:$B$6=C$1))

and copy across and down to occupy C3:J6. Of course, your real data
will occupy more than 6 rows in Sheet1, so adjust the ranges
accordingly.

If you want to avoid showing a lot of zeros, apply conditional
formatting to C3:J6 such that if Cell Value is 0 (zero), then set the
foreground colour to white.

Another way would be to use a Pivot Table, but then I'm a formula
man !!.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome.

Pete