Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic pivot table data organizer?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic pivot table data organizer?
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_Na me,'01/07/2005' as [month], Mth01 as Budget FROM Data union all SELECT Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/08/2005', Mth02 FROM Data UNION ALL SELECT Data.Account,Data.Org,Data.Project,Data.Account_Na me,'01/09/2005', Mth03 FROM Data UNION ALL <snip UNION ALL SELECT Data.Account,Data.Org,Data.Project,Data.Account_Na me,'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... -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=522330 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic pivot table data organizer?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Generic Pivot Chart Column | Excel Discussion (Misc queries) | |||
Excel 2007 Macro specific table to generic table? | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
Pivot Table - Use Other Pivot Table as Data Source | Excel Discussion (Misc queries) |