Pivot table novice

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 20 Feb 2007 15:16 GMT

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

Reply to this Message

Debra Dalgleish - 20 Feb 2007 19:34 GMT

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.

> 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
[quoted text clipped - 19 lines]
> Thanks in anticipation
> Michael

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Herbert Seidenberg - 21 Feb 2007 02:44 GMT

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

Reply to this Message