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

[ycz0352]

Guys,

I have the following data table...

Inv_Month</SPAN>

Description</SPAN>

Company</SPAN>

Labor Category</SPAN>

Rate</SPAN>

Bid Rate</SPAN>

CLIN_0001</SPAN>

CLIN_0002</SPAN>

CLIN_0003</SPAN>

CLIN_0004</SPAN>

CLIN_0005</SPAN>

CLIN_0006</SPAN>

CLIN_0007</SPAN>

CLIN_0008</SPAN>

CLIN_0009</SPAN>

CLIN_0010</SPAN>

10/1/12</SPAN>

Joe Smith</SPAN>

ABC</SPAN>

VP</SPAN>

$50 </SPAN>

$50 </SPAN>

1.0</SPAN>

2.0</SPAN>

3.0</SPAN>

4.0</SPAN>

5.0</SPAN>

6.0</SPAN>

7.0</SPAN>

8.0</SPAN>

9.0</SPAN>

10.0</SPAN>

10/1/12</SPAN>

Ed Jones</SPAN>

CDE</SPAN>

VP2</SPAN>

$25 </SPAN>

$10 </SPAN>

10.0</SPAN>

9.0</SPAN>

8.0</SPAN>

7.0</SPAN>

6.0</SPAN>

5.0</SPAN>

4.0</SPAN>

3.0</SPAN>

2.0</SPAN>

1.0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=10></COLGROUP>

That I need to transposed to this:

Inv_Month</SPAN>	Description</SPAN>	Company</SPAN>	Labor Category</SPAN>	Rate</SPAN>	Bid Rate</SPAN>	CLIN</SPAN>	Hours</SPAN>
10/1/12</SPAN>	Ed Jones</SPAN>	CDE</SPAN>	VP2</SPAN>	$25 </SPAN>	$10 </SPAN>	0001</SPAN>	10.0</SPAN>
10/1/12</SPAN>	Ed Jones</SPAN>	CDE</SPAN>	VP2</SPAN>	$25 </SPAN>	$10 </SPAN>	0002</SPAN>	9.0</SPAN>
10/1/12</SPAN>	Ed Jones</SPAN>	CDE</SPAN>	VP2</SPAN>	$25 </SPAN>	$10 </SPAN>	0003</SPAN>	8.0</SPAN>
10/1/12</SPAN>	Ed Jones</SPAN>	CDE</SPAN>	VP2</SPAN>	$25 </SPAN>	$10 </SPAN>	0004</SPAN>	7.0</SPAN>
10/1/12</SPAN>	Joe Smith</SPAN>	ABC</SPAN>	VP</SPAN>	$50 </SPAN>	$50 </SPAN>	0001</SPAN>	1.0</SPAN>
10/1/12</SPAN>	Joe Smith</SPAN>	ABC</SPAN>	VP</SPAN>	$50 </SPAN>	$50 </SPAN>	0002</SPAN>	2.0</SPAN>
10/1/12</SPAN>	Joe Smith</SPAN>	ABC</SPAN>	VP</SPAN>	$50 </SPAN>	$50 </SPAN>	0003</SPAN>	3.0</SPAN>
10/1/12</SPAN>	Joe Smith</SPAN>	ABC</SPAN>	VP</SPAN>	$50 </SPAN>	$50 </SPAN>	0004</SPAN>	4.0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=2></COLGROUP>

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

	A	B	C	D	E	F	G	H	I	J
1	Inv_Month	Description	Company	Labor Category	Rate	Bid Rate	CLIN_0001	CLIN_0002	CLIN_0003	CLIN_0004
2	10/01/2012	Joe Smith	ABC	VP	$50	$50	1	2	3	4
3	10/01/2012	Ed Jones	CDE	VP2	$25	$10	10	9	8	7

<tbody>
</tbody>

Excel 2010

The output:

Sheet2

	A	B	C	D	E	F	G	H
1	Inv_Month	Description	Company	Labor Category	Rate	Bid Rate	CLIN	Hours
2	10/01/2012	Joe Smith	ABC	VP	$50	$50	0001	1
3	10/01/2012	Joe Smith	ABC	VP	$50	$50	0002	2
4	10/01/2012	Joe Smith	ABC	VP	$50	$50	0003	3
5	10/01/2012	Joe Smith	ABC	VP	$50	$50	0004	4
6	10/01/2012	Ed Jones	CDE	VP2	$25	$10	0001	10
7	10/01/2012	Ed Jones	CDE	VP2	$25	$10	0002	9
8	10/01/2012	Ed Jones	CDE	VP2	$25	$10	0003	8
9	10/01/2012	Ed Jones	CDE	VP2	$25	$10	0004	7

<tbody>
</tbody>

Excel 2010

Worksheet Formulas 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> </tbody>

<tbody>
</tbody>

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.