Pivot table novice

[Michael]

As a teacher we tried a new data analysis tool this year in my school
which worked really well... except for the fact that it meant manually
filling in a table.
I'm sure there must be a more efficient electronic way of doing it, but
I'm not really an expert on these things.

I want to be able to put in a list of data: pupils names with scores
from two consecutive tests. Then I want to produce a table with test 1
and test 2 as the headers, and pupils' initials appearing in the
relevant box. I have managed to create a Pivot Table to show what I
want, except of course, it totals the number of pupils in each box,
rather than the individuals' names:

http://tesfaq.atspace.com/images/dataquery.htm

Can anyone explain if its possible to amend this so that, for example,
in row 4a/column 5b instead of "2", I get the initials of the relevant
pupils (CD & MN)

Or am I asking for too much?

Thanks in anticipation
Michael

 

[Debra Dalgleish]

The data area of a pivot table won't show the text from the source data.
You could put one test field, and the pupil names field, in the row area.
Put the other test field in the column area
Put another copy of pupil names in the data area, where it will show a
count.
Not exactly what you want, but it would give you a quick summary.

 

[Herbert Seidenberg]

I assume the base data at A2 looks like this:

Pupil L_2006 L_2007 PN
AB 4c 4a 1
BC 4b 5c 2
CD 4a 5b 3
DE 4b 5b 4
EF 4b 5c 5
FG 4c 5c 6
GH 4a 5c 7
HJ 4a 5a 8
JK 3a 4b 9
KL 3a 4c 10
LM 4c 4c 11
MN 4a 5b 12

PN is a helper column of unique, sequencial numbers.
Name the 4 columns.
To translate your existing PT,
located at A21

Count of Pupil L_2007
L_2006 4a 4b 4c 5a 5b 5c
3a 1 1
4a 1 2 1
4b 1 2
4c 1 1 1

into this table, located at A31

TB2 4a 4b 4c 5a 5b 5c
3a JK KL
4a HJ MN GH
4b DE EF
4c AB LM FG

Copy the headers to the new location
and enter at B32 this array formula:
=IF(B23>0,LOOKUP(LARGE(IF((L_2006=$A32)*(L_2007=B$31),PN,0),
1),PN,Pupil),"")
Fill the rest of the array with the fill handle.
Since some cells are occupied by more than one pupil,
run another table (at A38) to identify the second occupant,
using this formula:
=IF(B23>1,LOOKUP(LARGE(IF((L_2006=$A39)*(L_2007=B$31),PN,0),
2),PN,Pupil),"")
Concatenate the two tables to get this:

TB3 4a 4b 4c 5a 5b 5c
3a JK KL
4a HJ MN,CD GH
4b DE EF,BC
4c AB LM FG

 

[jonjparr]

I assume the base data at A2 looks like this:

Pupil L_2006 L_2007 PN
AB 4c 4a 1
BC 4b 5c 2
CD 4a 5b 3
DE 4b 5b 4
EF 4b 5c 5
FG 4c 5c 6
GH 4a 5c 7
HJ 4a 5a 8
JK 3a 4b 9
KL 3a 4c 10
LM 4c 4c 11
MN 4a 5b 12

PN is a helper column of unique, sequencial numbers.
Name the 4 columns.
To translate your existing PT,
located at A21

Count of Pupil L_2007
L_2006 4a 4b 4c 5a 5b 5c
3a 1 1
4a 1 2 1
4b 1 2
4c 1 1 1

into this table, located at A31

TB2 4a 4b 4c 5a 5b 5c
3a JK KL
4a HJ MN GH
4b DE EF
4c AB LM FG

Copy the headers to the new location
and enter at B32 this array formula:
=IF(B23>0,LOOKUP(LARGE(IF((L_2006=$A32)*(L_2007=B$31),PN,0),
1),PN,Pupil),"")
Fill the rest of the array with the fill handle.
Since some cells are occupied by more than one pupil,
run another table (at A38) to identify the second occupant,
using this formula:
=IF(B23>1,LOOKUP(LARGE(IF((L_2006=$A39)*(L_2007=B$31),PN,0),
2),PN,Pupil),"")
Concatenate the two tables to get this:

TB3 4a 4b 4c 5a 5b 5c
3a JK KL
4a HJ MN,CD GH
4b DE EF,BC
4c AB LM FG

Herbert, thanks for posting this. I've been searching on the Internet for a very long time about how to do this and stumbled across your response here. Is there any way you might be able to post a workbook showing how the instructions are followed as I'm struggling to recreate an example following your steps? If you could I would be extremely grateful.

Kind regards,
JonJParr