Excel - converting some columns' data into rows while leaving ohter columns in place.
Posted on 2012-08-22
I have MS Excel table with this layout (columns COMPANY,ID,COL1,COL2,COL3,COL4):
COMPANY ID COL1 COL2 COL3 COL4
comp1 0001 val11 val21 val31 val41
comp1 0002 val12 val22 val32 val42
comp1 0003 val13 val23 val33 val43
But I need to convert this table into the layout COMPANY,ID,COL1,NEWCOL1, NEWCOL2
(where NEWCOL1 would contain only header names COL2, COL3, COL4, and each ID would be repeated three times as there are COL2, COL3, COL4,
and NEWCOL2 would contain actual values for for COL2, COL3, COL4), like this:
COMPANY ID COL1 NEWCOL1 NEWCOL2
comp1 0001 val11 COL2 val21
comp1 0001 val11 COL3 val31
comp1 0001 val11 COL4 val41
comp1 0002 val12 COL2 val22
comp1 0002 val12 COL3 val32
comp1 0002 val12 COL4 val42
comp1 0003 val13 COL2 val23
comp1 0003 val13 COL3 val33
comp1 0003 val13 COL4 val43
Is it possible to do such conversion in Excel?
Obviously, the file contains many records, for example, a thousand different IDs, so a manual conversion is not an option - some Excel formulas or tools are needed if there are any.