arrays and pivot table

[Carsten]

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

 

[Harlan Grove]

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$77)-1,4)+3)

Fill A77 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).