Generic pivot table data organizer?

[gromit12]

Hi,

I have a table of the format of table 1 below that I need to get into
the format of table 2 below ready for being used as a pivot table. The
actual data set I'm using is MUCH bigger than this, and the thought of
doing this manually makes me shudder. I'm about to roll up my sleeves
to write some code to do it, but I can't help thinking someone must
have written some generic solution to this problem. Anyone ever come
across something like this?

Thanks in advance,

Graham

Table 1: BEFORE
Number Name Age Weight Gender
1 A 10 88 M
2 B 11 193 F
3 C 16 120 M
4 D 14 221 M
5 E 28 142 F
6 F 27 68 F
7 G 42 142 M
8 H 10 176 M
9 I 31 107 F
10 J 26 135 F
11 K 48 212 F
12 L 8 222 M
13 M 0 159 M

Table2:AFTER
Number Name Info InfoValue
1 A Age 10
2 B Age 11
3 C Age 16
4 D Age 14
5 E Age 28
6 F Age 27
7 G Age 42
8 H Age 10
9 I Age 31
10 J Age 26
11 K Age 48
12 L Age 8
13 M Age 0
1 A Weight 88
2 B Weight 193
3 C Weight 120
4 D Weight 221
5 E Weight 142
6 F Weight 68
7 G Weight 142
8 H Weight 176
9 I Weight 107
10 J Weight 135
11 K Weight 212
12 L Weight 222
13 M Weight 159
1 A Gender M
2 B Gender F
3 C Gender M
4 D Gender M
5 E Gender F
6 F Gender F
7 G Gender M
8 H Gender M
9 I Gender F
10 J Gender F
11 K Gender F
12 L Gender M
13 M Gender M

 

[steven1001]

The data conversion you describe is similar to having a budget worksheet
with accounts down the left hand side and months across the top and
needing to convert it to a single column of values with a column
containing the date for the value.
The Access SQL below performed this task. I assume you could use a
similar technique to recast your data. The source was a table called
'data' and its column headings were Account, Org, Project, Account_name
and then 12 columns of Mth01 to Mth12.

SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Name,'01/07/2005' as
[month], Mth01 as Budget
FROM Data
union all
SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Name,'01/08/2005',
Mth02
FROM Data
UNION ALL
SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Name,'01/09/2005',
Mth03
FROM Data
UNION ALL
<snip>
UNION ALL SELECT
Data.Account,Data.Org,Data.Project,Data.Account_Name,'01/06/2006',
Mth12
FROM Data;

I can not understand what kind of pivot table requirement would require
the sort of transformation of the underlying data in the way you
describe. Care to share?

regards...

 

[gromit12]

Thanks Steven,

First of all, thanks for the SQL idea - I may pursue it if I'm wrong
about the following:

I think in this case at least, you're right, I don't need to rearrange
the data at all. What I was trying to do was have Age and Weight, for
example, as separate series with Name on the category axis. By
arranging the table as in table2, I could just drag the Info field to
the series slot, the values into the Data Items area, and that worked
fine. When I tried the same using Table 1, and dragging Age, say, to
the series box, Excel was trying to plot each individual value as a
series. However, with your prompting I tried a few things and found
that if I dragged Age and Weight into the Data Items field, that works
just the same. Which I now realise is the more obvious way of using a
pivottable. Hopefully the same will hold for my more complex example. I
seem to have a knack for making the simple more complicated than it is!

Cheers

Graham