Progression matrix
 

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

Progression matrix
 

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

Progression matrix
 

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

On Dec 8, 11:40*pm, Nick Horn
wrote:
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

Progression matrix
 

Many thanks.

Regards

Nick

"Herbert Seidenberg" wrote:

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

Progression matrix
 

Many thanks

Regards

Nick

"Pete_UK" wrote:

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

On Dec 8, 11:40 pm, Nick Horn
wrote:
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

Progression matrix
 

You're welcome.

Pete

On Dec 9, 8:38*am, Nick Horn
wrote:
Many thanks

Regards

Nick