Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reversing date from column to Row
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reversing date from column to Row
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... -- HTH... Jim Thomlinson " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reversing date from column to Row
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 " wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reversing date from column to Row
Thank you. I tried both ways they both worked perfect.
Jim Thomlinson wrote: 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... -- HTH... Jim Thomlinson " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
date formula | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) |