Transform data.. pls help!

[gato88]

Hi

I have a table with the following columns:

Code	Name	Master Code
A	Test1	125
A	Test1	983
A	Test1	608
B	Test2	452
B	Test2	367
C	Test3	195
D...	...	....

<tbody>
</tbody>

I would like to reformat the data in this format:

Code	Master Code 1	Master Code 2	Master Code...
A	125	983	608
B	452	367
C	195
D...	...	...	...

<tbody>
</tbody>

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