Transform data.. pls help!

[gato88]

Hi

I have a table with the following columns:
[TABLE="class: cms_table, width: 500, align: left"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Master Code[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Test1[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Test1[/TD]
[TD]983[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Test1[/TD]
[TD]608[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Test2[/TD]
[TD]452[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Test2[/TD]
[TD]367[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Test3[/TD]
[TD]195[/TD]
[/TR]
[TR]
[TD]D...[/TD]
[TD]...[/TD]
[TD]....[/TD]
[/TR]
</tbody>[/TABLE]












I would like to reformat the data in this format:
[TABLE="class: cms_table, width: 500, align: left"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Master Code 1[/TD]
[TD]Master Code 2[/TD]
[TD]Master Code...[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]125[/TD]
[TD]983[/TD]
[TD]608[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]452[/TD]
[TD]367[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]195[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]








I tried using the CrossTab Query function but it comes up with a "too many headers" error.

Your assistance is much appreciated. Thanks!

 

[Aladin Akyurek]

The first exhibit is in A:C.

The second starts in E and E houses distinct codes of interest.

F2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($C$2:$C$100,SMALL(IF($A$2:$A$100=$E2,ROW($C$2:$C$100)-ROW($C$2)+1),COLUMNS($F2:F2))),"")

 

[SydneyGeek]

Crosstabs have a limit of 255 fields. Use Aladin's formula solution in Excel as a workaround.

Denis