Reversing date from column to Row

T

tr2usa

I need to change data from this format
ItemNo PCAL PCBW PCCH
PH53015 43 43 16

to this format.
Item Code Value
PH53015 PCAL 43
PH53015 PCBW 43
PH53015 PCCH 16

Can some one help me out?

Thanks
 
J

Jim Thomlinson

Put the data in a pivot table. Place the cell anywhere in the middle of your
data and select Data -> Pivot Table. When the wizard comes up you can
probably just select Finish as the default settings shoud be good enough. it
will create a new sheet with a pivot table on it. Place the Items in the left
column, the codes in the row across the top and the values in the middle...
 
T

Toppers

Try these, assuming data starts in row 2:

Item No:
=INDIRECT("A" &INT((ROW()-2)/3)+2)
Code:
=INDEX($B$1:$D$1,,MOD(ROW()-2,3)+1)
Value:
=OFFSET($B$2,INT((ROW()-2)/3),MOD(ROW()-2,3),1)

and copy all three down.

(I input the formulas on the same sheet in columns F to G; if you put them
on a separate sheet, qualify the above with the sheet name.

HTH
 
Top