Hi i would take to go from this form:
– x1 x2
y1 a b
y2 c d
to this form. In some kind of automated procces since i have many (many!) rows and coloumns:
y1 x1 a
y1 x2 b
y2 x1 c
y2 x2 d
What is this process called? And can it be done painless in excel? Or should i rather do it in SAS/SPSS/Stata?
Last edited by bonna; 05-09-2011 at 02:25 PM. Reason: typo
Don't ask me why it works, but it does
Give your first column a heading
Excel's PivotTable Wizard
1) Start the wizard -- Data/PivotTable & PivotChart Report...
2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]
3) Step 2a of 3 - Select Option Button: I will create the page fields -- [Next]
4) Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize -- [Add] -- [Next]
5) Step 3 of 3 - [Layout...]
6) Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]
7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL
I think the method is correct (Multiple Consolidation Ranges), but for some strange reason this option has not been added to Excel:Mac 2011. So this i unfortunatly not an option for me. Do you have any other suggestions, maybe a macro?
Not really - It would be nice though if in the future you added that you were looking for a Mac solution
Best add it and to your title, and to your MSoffice version
In VBA:
Please Login or Register to view this content.
Thanks, that was exactly what is was looking for.
Based on some other posts, i was able to modify it such that it places the new version in a seperate sheet. And as a second step i run the "Text to Columns..." to split the columns.
Modified VBA code:
Thx for the help.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks