Converting list to table

[mattstopel]

Hi all,

I have the following set of data:

[TABLE="width: 205"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Event[/TD]
[TD]Place[/TD]
[/TR]
[TR]
[TD]Athlete 1[/TD]
[TD]A[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 1[/TD]
[TD]C[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 1[/TD]
[TD]D[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 1[/TD]
[TD]E[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 2[/TD]
[TD]C[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]Athlete 2[/TD]
[TD]D[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]Athlete 3[/TD]
[TD]A[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]Athlete 4[/TD]
[TD]E[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]Athlete 4[/TD]
[TD]F[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 5[/TD]
[TD]B[/TD]
[TD]1st[/TD]
[/TR]
</tbody>[/TABLE]


And I would like to convert to to the following format.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD]Athlete 1[/TD]
[TD]1st[/TD]
[TD][/TD]
[TD]1st[/TD]
[TD]1st[/TD]
[TD]1st[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Athlete 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2nd[/TD]
[TD]2nd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Athlete 3[/TD]
[TD]2nd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Athlete 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2nd[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]Athlete 5[/TD]
[TD][/TD]
[TD]1st[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm not sure where to start, i have approx 200 athletes and a total of 10 events.

Is this a macro or formula job?

Thanks for any assistance!

Regard

 

[sheetspread]

Set a pivot table with name in rows, event in columns, and sum of place (delete the ordinal suffix) as value

 

[mattstopel]

Perfect, thank you, New to pivot tables and didn't know you could change to sum rather than count.