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

[contra3]

Wk#	A	H	A W%	H W%
1	CHN	PIT	.577	.488
1	TBA	CHN	.508	.505
2	TBA	MIL	.435	.449
2	CHN	TBA	.461	.507
2	KCA	TBA	.471	.424

<colgroup><col width="64" span="3" style="width:48pt"> <col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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:

Team	Wk#	1	2	3	4	5	6	7	8
CHN	1	.577	.505
CHN	2	.461
TBA	1	.508
TBA	2	.435	.507	.424

<colgroup><col width="64" span="2" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

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