Transposing data from one format to another

[Shaun_Lin]

Hi,

I am trying to analyze some data for my company but the manner which the manner which the data is being arranged now makes it difficult for me to plot charts. What I need is to change the format from the original to the new table (see below). Any help will be much appreciated!


Original table

[TABLE="width: 429"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION A[/TD]
[TD]LOCATION B[/TD]
[TD]LOCATION C[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

New table

[TABLE="width: 352"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]


Regards,

Shaun Lin

 

[gallen]

Either I'm confused or you are.

Surely
[TABLE="class: cms_table, width: 429"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION A[/TD]
[TD]LOCATION B[/TD]
[TD]LOCATION C[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Should become

[TABLE="class: cms_table, width: 352"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[Shaun_Lin]

Hi there,

Sorry about it. You are right, there was a typo on my side. Please find updated table below. Thanks.

Original table

[TABLE="class: cms_table, width: 429"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION A[/TD]
[TD]LOCATION B[/TD]
[TD]LOCATION C[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]


New table

[TABLE="class: cms_table, width: 352"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]ITEM B[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM C[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

Either I'm confused or you are.

Surely
[TABLE="class: cms_table, width: 429"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION A[/TD]
[TD]LOCATION B[/TD]
[TD]LOCATION C[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Should become

[TABLE="class: cms_table, width: 352"]
<tbody>[TR]
[TD]ITEM CODE[/TD]
[TD]LOCATION[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION B[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]ITEM A[/TD]
[TD]LOCATION C[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[azumi]

Assumming your table is on ranges A1 to D4 (include column header):

Put this on cell E2 and copied down to E4 as "Helper Column":
=SUMPRODUCT(($A$2:$A$4=A2)*($B$2:$D$4<>""))

And put this on A6 and copied down as necessary:
=LOOKUP(ROW(A1),SUMIF(OFFSET(E$1,,,ROW($1:$99),),"<>")+1,A$2:A$99)&""

And put this on B6 and entered as array formula after that copied down as necessary:
=IFERROR(INDEX($B$1:$D$1,MOD(SMALL(IF($B$2:$D$4<>"",(ROW($B$2:$D$4)-ROW($B$2)+1)*10^5+COLUMN($B$2:$D$4)-COLUMN($B$2)+1),ROWS($A$1:A1)),10^5)),"")

Last put this om C6 and copied down as necessary:
=IFERROR(INDEX($B$2:$D$4,MATCH(A6,$A$2:$A$4,0),MATCH(B6,$B$1:$D$1,0)),"")