Need Help with a Macro to Transpose Data PLEASE!!!

[ycz0352]

Guys,

I have the following data table...

[TABLE="width: 933"]
<TBODY>[TR]
[TD]Inv_Month</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Company</SPAN>[/TD]
[TD]Labor Category</SPAN>[/TD]
[TD]Rate</SPAN>[/TD]
[TD]Bid Rate</SPAN>[/TD]
[TD]CLIN_0001</SPAN>[/TD]
[TD]CLIN_0002</SPAN>[/TD]
[TD]CLIN_0003</SPAN>[/TD]
[TD]CLIN_0004</SPAN>[/TD]
[TD]CLIN_0005</SPAN>[/TD]
[TD]CLIN_0006</SPAN>[/TD]
[TD]CLIN_0007</SPAN>[/TD]
[TD]CLIN_0008</SPAN>[/TD]
[TD]CLIN_0009</SPAN>[/TD]
[TD]CLIN_0010</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Joe Smith</SPAN>[/TD]
[TD]ABC</SPAN>[/TD]
[TD]VP</SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]1.0</SPAN>[/TD]
[TD="align: right"]2.0</SPAN>[/TD]
[TD="align: right"]3.0</SPAN>[/TD]
[TD="align: right"]4.0</SPAN>[/TD]
[TD="align: right"]5.0</SPAN>[/TD]
[TD="align: right"]6.0</SPAN>[/TD]
[TD="align: right"]7.0</SPAN>[/TD]
[TD="align: right"]8.0</SPAN>[/TD]
[TD="align: right"]9.0</SPAN>[/TD]
[TD="align: right"]10.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Ed Jones</SPAN>[/TD]
[TD]CDE</SPAN>[/TD]
[TD]VP2</SPAN>[/TD]
[TD="align: right"]$25 </SPAN>[/TD]
[TD="align: right"]$10 </SPAN>[/TD]
[TD="align: right"]10.0</SPAN>[/TD]
[TD="align: right"]9.0</SPAN>[/TD]
[TD="align: right"]8.0</SPAN>[/TD]
[TD="align: right"]7.0</SPAN>[/TD]
[TD="align: right"]6.0</SPAN>[/TD]
[TD="align: right"]5.0</SPAN>[/TD]
[TD="align: right"]4.0</SPAN>[/TD]
[TD="align: right"]3.0</SPAN>[/TD]
[TD="align: right"]2.0</SPAN>[/TD]
[TD="align: right"]1.0</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=10></COLGROUP>[/TABLE]

That I need to transposed to this:

[TABLE="width: 464"]
<TBODY>[TR]
[TD]Inv_Month</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Company</SPAN>[/TD]
[TD]Labor Category</SPAN>[/TD]
[TD]Rate</SPAN>[/TD]
[TD]Bid Rate</SPAN>[/TD]
[TD]CLIN</SPAN>[/TD]
[TD]Hours</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Ed Jones</SPAN>[/TD]
[TD]CDE</SPAN>[/TD]
[TD]VP2</SPAN>[/TD]
[TD="align: right"]$25 </SPAN>[/TD]
[TD="align: right"]$10 </SPAN>[/TD]
[TD]0001</SPAN>[/TD]
[TD="align: right"]10.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Ed Jones</SPAN>[/TD]
[TD]CDE</SPAN>[/TD]
[TD]VP2</SPAN>[/TD]
[TD="align: right"]$25 </SPAN>[/TD]
[TD="align: right"]$10 </SPAN>[/TD]
[TD]0002</SPAN>[/TD]
[TD="align: right"]9.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Ed Jones</SPAN>[/TD]
[TD]CDE</SPAN>[/TD]
[TD]VP2</SPAN>[/TD]
[TD="align: right"]$25 </SPAN>[/TD]
[TD="align: right"]$10 </SPAN>[/TD]
[TD]0003</SPAN>[/TD]
[TD="align: right"]8.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Ed Jones</SPAN>[/TD]
[TD]CDE</SPAN>[/TD]
[TD]VP2</SPAN>[/TD]
[TD="align: right"]$25 </SPAN>[/TD]
[TD="align: right"]$10 </SPAN>[/TD]
[TD]0004</SPAN>[/TD]
[TD="align: right"]7.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Joe Smith</SPAN>[/TD]
[TD]ABC</SPAN>[/TD]
[TD]VP</SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD]0001</SPAN>[/TD]
[TD="align: right"]1.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Joe Smith</SPAN>[/TD]
[TD]ABC</SPAN>[/TD]
[TD]VP</SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD]0002</SPAN>[/TD]
[TD="align: right"]2.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Joe Smith</SPAN>[/TD]
[TD]ABC</SPAN>[/TD]
[TD]VP</SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD]0003</SPAN>[/TD]
[TD="align: right"]3.0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/12</SPAN>[/TD]
[TD]Joe Smith</SPAN>[/TD]
[TD]ABC</SPAN>[/TD]
[TD]VP</SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD="align: right"]$50 </SPAN>[/TD]
[TD]0004</SPAN>[/TD]
[TD="align: right"]4.0</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]


Any assistance is greatly appreciated!

 

[circledchicken]

Hi and welcome to the forum,

Does it have to be a macro or would a formula solution suffice?
A possible formula approach might be as follows.

Your initial setup:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Inv_Month[/TD]
[TD]Description[/TD]
[TD]Company[/TD]
[TD]Labor Category[/TD]
[TD]Rate[/TD]
[TD]Bid Rate[/TD]
[TD]CLIN_0001[/TD]
[TD]CLIN_0002[/TD]
[TD]CLIN_0003[/TD]
[TD]CLIN_0004[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Joe Smith[/TD]
[TD]ABC[/TD]
[TD]VP[/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Ed Jones[/TD]
[TD]CDE[/TD]
[TD]VP2[/TD]
[TD]$25[/TD]
[TD]$10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

The output:

Sheet2[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Inv_Month[/TD]
[TD]Description[/TD]
[TD]Company[/TD]
[TD]Labor Category[/TD]
[TD]Rate[/TD]
[TD]Bid Rate[/TD]
[TD]CLIN[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Joe Smith[/TD]
[TD]ABC[/TD]
[TD]VP[/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD="align: right"]0001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Joe Smith[/TD]
[TD]ABC[/TD]
[TD]VP[/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD="align: right"]0002[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Joe Smith[/TD]
[TD]ABC[/TD]
[TD]VP[/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD="align: right"]0003[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Joe Smith[/TD]
[TD]ABC[/TD]
[TD]VP[/TD]
[TD]$50[/TD]
[TD]$50[/TD]
[TD="align: right"]0004[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Ed Jones[/TD]
[TD]CDE[/TD]
[TD]VP2[/TD]
[TD]$25[/TD]
[TD]$10[/TD]
[TD="align: right"]0001[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Ed Jones[/TD]
[TD]CDE[/TD]
[TD]VP2[/TD]
[TD]$25[/TD]
[TD]$10[/TD]
[TD="align: right"]0002[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Ed Jones[/TD]
[TD]CDE[/TD]
[TD]VP2[/TD]
[TD]$25[/TD]
[TD]$10[/TD]
[TD="align: right"]0003[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]10/01/2012[/TD]
[TD]Ed Jones[/TD]
[TD]CDE[/TD]
[TD]VP2[/TD]
[TD]$25[/TD]
[TD]$10[/TD]
[TD="align: right"]0004[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
A2	=INDEX(Sheet1!A$2:A$3, ROUNDUP(ROWS(A$1:A1)/4,0))
G2	=SUBSTITUTE(INDEX(Sheet1!$G$1:$J$1, MOD(ROWS(A$1:A4),4)+1),"CLIN_","")
H2	=INDEX(Sheet1!$G$2:$P$3, ROUNDUP(ROWS(F$1:F1)/4,0), MOD(ROWS(A$1:A4),4)+1)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
Note:

• Change the formulae ranges accordingly as appropriate for your entire dataset.
• Drag the formulae in A2 across to F2 and down as far as required. Drag the formulae in G2 and H2 down as far as required.
• The 4 in the formulae refer to 4 CLIN numbers, you can change this to 10 and adjust the Sheet1 column references (the first argument of the INDEX function) if you want all ten.
• You can copy and paste the results as values over your original data if that is what you want.