Home |
Search |
Today's Posts |
#1
|
|||
|
|||
arrays and pivot table
I have a worksheet in the following format:
January February March April Salesman A Product A 22 11 3 6 Salesman B Product A 2 33 5 3 Salesman B Product B 6 4 6 7 I'd like to use a pivotable on this. To do that I am trying to convert the above into something like Salesman A Product A January 2 Salesman B Product A January 3 Salesman B Product B February 4 etc. Now first of all, am I going the wrong way about this or is there a way of getting the pivottable from the fist array. If not, how do I convert the first array into the second. Cheers, Carsten |
#2
|
|||
|
|||
"Carsten" wrote...
I have a worksheet in the following format: January February March April Salesman A Product A 22 11 3 6 Salesman B Product A 2 33 5 3 Salesman B Product B 6 4 6 7 I'd like to use a pivotable on this. To do that I am trying to convert the above into something like Salesman A Product A January 2 Salesman B Product A January 3 Salesman B Product B February 4 etc. You can't use a pivot table to do this. It looks like you want to un-crosstab, but your sample result doesn't match your sample original data (the only 3 in input is for Salesman A Product A in March, not Salesman B Product A in January). To un-crosstab, if the original data were in a range named XTab including the row with the month names at the top, and the top-left result cell were A7, try the following formulas. A7: =INDEX(XTab,INT((ROWS(A$7:A7)-1)/4)+2,1) B7: =INDEX(XTab,INT((ROWS(B$7:B7)-1)/4)+2,2) C7: =INDEX(XTab,1,MOD(ROWS(C$7:C7)-1,4)+3) D7: =INDEX(XTab,INT((ROWS(B$7:B7)-1)/4)+2,MOD(ROWS(D$7:D7)-1,4)+3) Fill A7:D7 down as far as needed. When you've exhausted the original data, the formulas in columns A, B and D will return #REF! errors. This will product a table in the original salesman and product order, not sorted by month. If you want it sorted by month, select the entire result range, copy it, then paste special as values on top of itself, and sort it by the month column (C). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating % in a pivot table | Excel Worksheet Functions |