Need a LOOKUP-like function to extract data from columns into new table

[contra3]

[TABLE="width: 320"]
<colgroup><col width="64" span="3" style="width:48pt"> <col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Wk#[/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[TD="class: xl68, width: 64"]A W%[/TD]
[TD="class: xl68, width: 64"]H W%[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]CHN[/TD]
[TD="class: xl65"]PIT[/TD]
[TD="class: xl67"].577[/TD]
[TD="class: xl67"].488[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl65"]CHN[/TD]
[TD="class: xl67"].508[/TD]
[TD="class: xl67"].505[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl65"]MIL[/TD]
[TD="class: xl67"].435[/TD]
[TD="class: xl67"].449[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]CHN[/TD]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl67"].461[/TD]
[TD="class: xl67"].507[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]KCA[/TD]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl67"].471[/TD]
[TD="class: xl67"].424[/TD]
[/TR]
</tbody>[/TABLE]

The data looks like the above, with a few thousand rows, Wk# 1-30, 30 Teams.

I want to extract it to look like this:

[TABLE="width: 640"]
<colgroup><col width="64" span="2" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Team[/TD]
[TD="class: xl66, width: 64"]Wk#[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl66, width: 64"]4[/TD]
[TD="class: xl66, width: 64"]5[/TD]
[TD="class: xl66, width: 64"]6[/TD]
[TD="class: xl66, width: 64"]7[/TD]
[TD="class: xl66, width: 64"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]CHN[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl67"].577[/TD]
[TD="class: xl67"].505[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl65"]CHN[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl67"].461[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl67"].508[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl65"]TBA[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl67"].435[/TD]
[TD="class: xl67"].507[/TD]
[TD="class: xl67"].424[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]


So that the 1-8 column headers are simply instances of occurences of the Team and Wk# combinations, and in each cell in that row it lists the w% from the first table. Also, note that if the team is H (home) in the first dataset, the H W% is used. If the team is A (away), the A W% data is used.

There will be between 4 and 8 data points for each Team/Wk# combination, in general.

Pretty lost here. Have tried various if statements with lookups, match, etc. The only way I can make this work is to create individual tables for each Team and Wk# combination, but that results in over 1000 tables and is very cumbersome.

Thank you in advance for any direction you can provide.

 

[mgrady11]

Try using the Transpose() function.

Excel TRANSPOSE Function