Help with Transpose Macro

shyy · Sep 18, 2013

Hi everyone,

I have a database that gets exported from a tax program. Can someone help me create a macro to transpose the data information. Using Column A an anchor point. This can consists of 16,000 rows.

Thanks in advance

This is how the data looks:

Excel 2010

	A	B	C	D	E	F
AP	Partner 1
Partner 2
Partner 3
Partner 4
Partner 5
CC	Partner 1
Partner 2
Partner 3
Partner 4
Partner 5
DD	Partner 1
Partner 2
Partner 3
Partner 4
Partner 5

<TBODY>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]0.00657304
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]0.00328652
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]0.13146076
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]

[TD="align: right"]0.00438202
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]

[TD="align: right"]0.0004382
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]95
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4272
[/TD]

[TD="align: center"]96
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2136
[/TD]

[TD="align: center"]97
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]85450
[/TD]

[TD="align: center"]98
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2848
[/TD]

[TD="align: center"]99
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]285
[/TD]

[TD="align: center"]189
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10846
[/TD]

[TD="align: center"]190
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5423
[/TD]

[TD="align: center"]191
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]216909
[/TD]

[TD="align: center"]192
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7230
[/TD]

[TD="align: center"]193
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]723
[/TD]

</TBODY>

SA

Desired Result:

Excel 2010

	A	B	C	D	E
AP	Partner 1	CC	Partner 1	DD	Partner 1
Partner 2	Partner 2	Partner 2
Partner 3	Partner 3	Partner 3
Partner 4	Partner 4	Partner 4
Partner 5	Partner 5	Partner 5

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.00657304[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4272[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10846[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]0.00328652[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2136[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5423[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]0.13146076[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]85450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]216909[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]0.00438202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2848[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7230[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]0.0004382[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]285[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]723[/TD]

</TBODY>

SA

Comfy · Sep 18, 2013

Something could certainly written but may I ask why you need the output to look as it does?

I would say an easier/usable format would look like the following:

Excel 2010

A

B

C

D

E

1

Count

Partner

AP

CC

DD

2

1

Partner 1

0.00657304

4272

10846

3

2

Partner 2

0.00328652

2136

5423

4

3

Partner 3

0.13146076

85450

216909

5

4

Partner 4

0.00438202

2848

7230

6

5

Partner 5

0.0004382

285

723

Sheet2

MickG · Sep 18, 2013

Try this:-
Your Data assumed to start row1
Results sheet2, starting "A2"

Code:

[COLOR="Navy"]Sub[/COLOR] MG18Sep09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(Rng.Count)
ReDim ray(1 To Rng.Count, 1 To 1)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    [COLOR="Navy"]If[/COLOR] Not Dn = vbNullString [COLOR="Navy"]Then[/COLOR]
        Temp = Dn
        c = 0
        col = col + Lst + 1
        ReDim Preserve ray(1 To Rng.Count, 1 To col)
    [COLOR="Navy"]End[/COLOR] If
        c = c + 1
        [COLOR="Navy"]For[/COLOR] Ac = 1 To Lst
            ray(c, col + Ac - Lst - 1) = Dn(, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
    Sheets("Sheet2").Range("A2").Resize(c, col) = ray
    MsgBox "run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

shyy · Sep 18, 2013

Thanks MickG,

It worked but can you do one small modification. Remove the extra columns betweent the allocation code and partner ID. (Column B, H, O)

This is the output:

Excel 2010

	A	B	C	D	E

AP	Partner 1	CC	Partner 1	DD	Partner 1
Partner 2	Partner 2	Partner 2
Partner 3	Partner 3	Partner 3
Partner 4	Partner 4	Partner 4
Partner 5	Partner 5	Partner 5

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.00657304[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4272[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]0.00328652[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2136[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]0.13146076[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]85450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]0.00438202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2848[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]0.0004382[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]285[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

</TBODY>

Sheet2

With column Removed:

Excel 2010

A

C

D

E

F

G

I

J

K

L

M

N

P

Q

1

2

AP

1

Partner 1

0.00657304

CC

1

Partner 1

4272

DD

1

Partner 1

3

2

Partner 2

0.00328652

2

Partner 2

2136

2

Partner 2

4

3

Partner 3

0.13146076

3

Partner 3

85450

3

Partner 3

5

4

Partner 4

0.00438202

4

Partner 4

2848

4

Partner 4

6

5

Partner 5

0.0004382

5

Partner 5

285

5

Partner 5

Sheet2

shyy · Sep 18, 2013

Thanks for suggestion! The reason why this might not work, is each allocation code uses either a percentage or an amount. The column to the left contains the percentage, so if we don't use that, it shows up blank but is a placeholder incase their is a ration.

Comfy said:
Something could certainly written but may I ask why you need the output to look as it does?

I would say an easier/usable format would look like the following:

Excel 2010

A
B
C
D
E

Count
Partner
AP
CC
DD

Partner 1

Partner 2

Partner 3

Partner 4

Partner 5

<TBODY>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]0.00657304
[/TD]
[TD="align: right"]4272
[/TD]
[TD="align: right"]10846
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]0.00328652
[/TD]
[TD="align: right"]2136
[/TD]
[TD="align: right"]5423
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]0.13146076
[/TD]
[TD="align: right"]85450
[/TD]
[TD="align: right"]216909
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]4
[/TD]

[TD="align: right"]0.00438202
[/TD]
[TD="align: right"]2848
[/TD]
[TD="align: right"]7230
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]5
[/TD]

[TD="align: right"]0.0004382
[/TD]
[TD="align: right"]285
[/TD]
[TD="align: right"]723
[/TD]

</TBODY>
Sheet2

MickG · Sep 18, 2013

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG18Sep20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nAc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(Rng.Count)
ReDim ray(1 To Rng.Count, 1 To 1)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    [COLOR="Navy"]If[/COLOR] Not Dn = vbNullString [COLOR="Navy"]Then[/COLOR]
        Temp = Dn
        c = 0
        col = col + Lst
        ReDim Preserve ray(1 To Rng.Count, 1 To col)
    [COLOR="Navy"]End[/COLOR] If
        c = c + 1
        [COLOR="Navy"]For[/COLOR] Ac = 1 To Lst
            nAc = IIf(Ac > 1, Ac + 1, Ac)
            ray(c, col + Ac - Lst) = Dn(, nAc)
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
    Sheets("Sheet2").Range("A2").Resize(c, col) = ray
    MsgBox "run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Help with Transpose Macro

shyy

Well-known Member

Comfy

Well-known Member

MickG

MrExcel MVP

shyy

Well-known Member

shyy

Well-known Member

MickG

MrExcel MVP

Similar threads

Share this page

Help with Transpose Macro

shyy

Well-known Member

Comfy

Well-known Member

MickG

MrExcel MVP

shyy

Well-known Member

shyy

Well-known Member

MickG

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock