Eclipse said:
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
