Crosstabing

[Eclipse]

Hi,
I have some data arrnaged this way:

Name Subject Grade
Peter Math A
Peter English B
Peter Science C
Sally English A
Sally Math D


How can i re-arrange them to give a crosstab them this in excel?


Name English Math Science
Peter A B C
Sally A D

etc?

I tried Pivot tables but that only seem to give you the count.

Hope you can help.

 

[Jay Petrulis]

Hi,
I have some data arrnaged this way:

Name Subject Grade
Peter Math A
Peter English B
Peter Science C
Sally English A
Sally Math D


How can i re-arrange them to give a crosstab them this in excel?


Name English Math Science
Peter A B C
Sally A D

etc?

I tried Pivot tables but that only seem to give you the count.

Hope you can help.

Here is how I would do this....

With your data in columns A-C, create defined names as follows (assume
Sheet1 and header row in row 1):

NmList refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
SubjList refers to: =OFFSET(NmList,0,1)
GradeList refers to: =OFFSET(NmList,0,2)
NmSubMatch refers to: =NmList&"|"&SubjList

In F1:H1, I had the unique subjects as headers
In E2 and down, I had the names of the students.

Select cell F2 and insert the following name
NameSubj refers to: =Sheet1!$E2&"|"&Sheet1!F$1

In the body of the table, I used the following formula
=INDEX(GradeList,MATCH(NameSubj,NmSubMatch,0))

for all entries.

I formatted the cells as General;General; (note the second semicolon)

to allow for grades not filled in to remain blank (the formula will
return a zero).

Entries not found will return a #N/A error unless you change the
formula to something like:

=IF(ISNA(MATCH(NameSubj,NmSubMatch,0)),"",INDEX(GradeList,MATCH(NameSubj,NmSubMatch,0)))

or, if you define the following name
MatchNameSubj refers to: =MATCH(NameSubj,NmSubMatch,0)

You get the following result

=IF(ISNA(MatchNameSubj),"",INDEX(GradeList,MatchNameSubj))


There are simpler ways of doing this, but this is flexible and dynamic.

HTH,
Jay

 

[Eclipse]

Hi Jay,

Thanks for that. I am not sure what this line means..

Select cell F2 and insert the following name
NameSubj refers to: =Sheet1!$E2&"|"&Sheet1!F$1

I have defined the rest and inserted the formula in the appropriate places,
but F2 isthe body of the table. Can you help further?

Here's what I have so far..

A B C D E
F G H
1 Name Subject Grade English Math Science
2 Peter Math A Peter #VALUE! #VALUE! #VALUE!
3 Peter English B Sally #VALUE! #VALUE! #VALUE!
4 Peter Science D
5 Sally Math A
6 Sally English A
7 Sally DT C




Thanks

Seb

 

[Jay Petrulis]

Hi Jay,

Thanks for that. I am not sure what this line means..

Hi,

If the first intersection in your crosstab is cell F2, corresponding to
Peter (cell E2) and Math (cell F1), select that cell (make it the
active cell.

Then go to Insert > Name > Define
call the name 'NameSubj' and use the above in the refers to box. The
defined name is used so that the end formula need not be array-entered.

Adjust the function definition to suit your actual data. Just make
sure that the column reference is anchored for the names and the row
reference is anchored for the subjects.

Also note, that since you are in the active sheet, you do not need to
enter the sheet names in the named formula.

=$E2&"|"&F$1

should suffice.

Regards,
Jay

 

[Eclipse]

Thanks alot, worked a treat.

Hi,

If the first intersection in your crosstab is cell F2, corresponding to
Peter (cell E2) and Math (cell F1), select that cell (make it the
active cell.

Then go to Insert > Name > Define
call the name 'NameSubj' and use the above in the refers to box. The
defined name is used so that the end formula need not be array-entered.

Adjust the function definition to suit your actual data. Just make
sure that the column reference is anchored for the names and the row
reference is anchored for the subjects.

Also note, that since you are in the active sheet, you do not need to
enter the sheet names in the named formula.

=$E2&"|"&F$1

should suffice.

Regards,
Jay