help to consolidate data
 

I have a listing of about 200 data points that includes 2 columns with test scores from 2 different years (2006 and 2007). I would like to generate a table similar to what is shown below to show how many students fall in each of the cells. This would be the total number of students in each scenario of getting one score one year and one score the following year. Is there a simple formula that would work for the entire table?

Here is the table I want given the data listed below.
1 2 3 4 5
1 1 1 0 0 0
2 1 2 0 0 0
3 0 3 1 1 0
4 0 0 0 1 0
5 0 0 1 0 0



DATA:

1 1
1 2
3 3
3 4
3 2
3 3
4 4
5 3
2 2
3 2
2 2
2 1
3 2

help to consolidate data
 

Assuming your results table has values 1 to 5 in B1 to F1 ans A2 to A6 and
Sheet1 is your source.

In B2: =SUMPRODUCT(--(Sheet1!$A$1:$A$200=$A2),--(Sheet1!$B$1:$B$200=B$1))

Copy across and down.

HTH

"docdutton" wrote:

I have a listing of about 200 data points that includes 2 columns with test scores from 2 different years (2006 and 2007). I would like to generate a table similar to what is shown below to show how many students fall in each of the cells. This would be the total number of students in each scenario of getting one score one year and one score the following year. Is there a simple formula that would work for the entire table?

Here is the table I want given the data listed below.
1 2 3 4 5
1 1 1 0 0 0
2 1 2 0 0 0
3 0 3 1 1 0
4 0 0 0 1 0
5 0 0 1 0 0



DATA:

1 1
1 2
3 3
3 4
3 2
3 3
4 4
5 3
2 2
3 2
2 2
2 1
3 2