Help with transpose

SeanDamnit · Aug 9, 2011

Hello collective consciousness of Mr. Excel,

I have an issue that I hope is relatively simple for someone out there.

I have a data set that looks a little like this:

a---1
a---2
a---3
b---4
c---5
c---6
d---7
d---8
d---9
d---10
.
.
.

and I'd like to make it look like this:

a---1---2---3
b---4
c---5---6
d---7---8---9---10
.
.
.

column 1 will always change...sometimes there will be 5 of the same, sometimes 1, sometimes 30. column 2 is a random number, not sequential if that matters.

I appreciate any guidance...thanks!

cgcamal · Aug 9, 2011

Hi Sean,

Try the following code in a copy of your data:
(The original columns A and B will be deleted)

Code:

Sub Transpose()
Dim Lr As Integer, i As Integer, j As Integer, m As Integer

Application.ScreenUpdating = False
Lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To Lr
    If IsEmpty(Cells(m + 1, 3)) Then
        Count = WorksheetFunction.CountIf(Columns("A"), Cells(i, "A"))
        Cells(m + 1, 3) = Cells(i, "A")
    End If
        Cells(m + 1, j + 4) = Cells(i, "B")
        j = j + 1
    If j = Count Then m = m + 1: j = 0
Next i

Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Hope this helps.

Regards.

villy · Aug 9, 2011

Try...

Code:

Sub transpose()
last = Cells(Rows.Count, "C").End(xlUp).Row
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
    MsgBox last
    With WorksheetFunction.Application
        If Not IsError(.Index(Range("C2:C" & last), _
            .Match(Range("A" & i), Range("C2:C" & last), 0), 1)) = True Then
            Set D_Rng = Range("D" & .Match(Range("A" & i), Range("C2:C" & last), 0) + 1)
            D_Rng.Value = D_Rng & "--" & Range("B" & i)
        Else
            Range("C" & last + 1).Value = Range("A" & i)
            Range("D" & last + 1).Value = Range("B" & i)
            last = last + 1
        End If
    End With
Next
Columns("A:B").Delete Shift:=xlToLeft
End Sub

Try it with:
Col A_______Col B _____Col C ____Col D
input_______data ______Output____Data
a ___________ 1
a ___________ 2
b ___________ 3
c ___________ 4
c ___________ 5
d ___________ 6

MrVillareal · Aug 9, 2011

HI friend

How about this.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

1

Raw Data

Count

ReturnValue

2

a

1

a

3

1

2

3

a

2

b

1

4

a

3

c

2

5

6

5

b

4

d

4

7

8

9

10

6

c

5

7

c

6

8

d

7

9

d

8

10

d

9

11

d

10

Sheet8

Excel 2010

Cell Formulas
Range	Formula
E2	=COUNTIF($A$2:$A$11,D2)
F2	=IF(COLUMNS($F2:F2)<=$E2,INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($A$2:$A$11=$D2),COLUMNS($F2:F2))),"")

For E2 copy down
For F2 copy across and down

Hope it help too.
HOpe for feedback

SeanDamnit · Aug 9, 2011

cgcamal said:

Hi Sean,

Try the following code in a copy of your data:
(The original columns A and B will be deleted)

Code:

Sub Transpose()
Dim Lr As Integer, i As Integer, j As Integer, m As Integer

Application.ScreenUpdating = False
Lr = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To Lr
    If IsEmpty(Cells(m + 1, 3)) Then
        Count = WorksheetFunction.CountIf(Columns("A"), Cells(i, "A"))
        Cells(m + 1, 3) = Cells(i, "A")
    End If
        Cells(m + 1, j + 4) = Cells(i, "B")
        j = j + 1
    If j = Count Then m = m + 1: j = 0
Next i

Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Hope this helps.

Regards.

Works great, thanks!

villy said:

Try...

Code:

Sub transpose()
last = Cells(Rows.Count, "C").End(xlUp).Row
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
    MsgBox last
    With WorksheetFunction.Application
        If Not IsError(.Index(Range("C2:C" & last), _
            .Match(Range("A" & i), Range("C2:C" & last), 0), 1)) = True Then
            Set D_Rng = Range("D" & .Match(Range("A" & i), Range("C2:C" & last), 0) + 1)
            D_Rng.Value = D_Rng & "--" & Range("B" & i)
        Else
            Range("C" & last + 1).Value = Range("A" & i)
            Range("D" & last + 1).Value = Range("B" & i)
            last = last + 1
        End If
    End With
Next
Columns("A:B").Delete Shift:=xlToLeft
End Sub

Try it with:
Col A_______Col B _____Col C ____Col D
input_______data ______Output____Data
a ___________ 1
a ___________ 2
b ___________ 3
c ___________ 4
c ___________ 5
d ___________ 6

This actually didn't work - a popup menu appeared simply saying "60", then everytime I hit "ok" a new popup appeared counting up to 100 something. The macro stopped after this.

MrVillareal said:
HI friend

How about this.
Excel Workbook
A B C D E F G H I J
1 Raw Data Count ReturnValue
2 a 1 a 3 1 2 3
3 a 2 b 1 4
4 a 3 c 2 5 6
5 b 4 d 4 7 8 9 10
6 c 5
7 c 6
8 d 7
9 d 8
10 d 9
11 d 10
Sheet8
Excel 2010
Cell Formulas
Range Formula
E2 =COUNTIF($A$2:$A$11,D2)
F2 =IF(COLUMNS($F2:F2)<=$E2,INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($A$2:$A$11=$D2),COLUMNS($F2:F2))),"")

For E2 copy down
For F2 copy across and down

Hope it help too.
HOpe for feedback

This didn't work either. I get a #NAME error...maybe because I have excel 2007, not 2010?

I'm very grateful that 3 different people offered help on this. Thanks much everyone!

MrVillareal · Aug 9, 2011

Knockout punch.

..
Try this one.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

1

Raw Data

Count

ReturnValue

2

a

1

a

3

1

2

3

a

2

b

1

4

a

3

c

2

5

6

5

b

4

d

4

7

8

9

10

6

c

5

7

c

6

8

d

7

9

d

8

10

d

9

11

d

10

Sheet8

Excel 2010

Cell Formulas
Range	Formula
E2	=COUNTIF($A$2:$A$11,D2)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

SeanDamnit · Aug 9, 2011

MrVillareal said:
Knockout punch...
Try this one.
Excel Workbook
A B C D E F G H I J
1 Raw Data Count ReturnValue
2 a 1 a 3 1 2 3
3 a 2 b 1 4
4 a 3 c 2 5 6
5 b 4 d 4 7 8 9 10
6 c 5
7 c 6
8 d 7
9 d 8
10 d 9
11 d 10
Sheet8
Excel 2010
Cell Formulas
Range Formula
E2 =COUNTIF($A$2:$A$11,D2)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

That works. I appreciate the help. Macro is probably easier overall, but I wasn't familiar with some of the functions you used here, so I still learned something

Thanks!

MrVillareal · Aug 9, 2011

Welcome

cgcamal · Aug 9, 2011

SeanDamnit said:
Works great, thanks!

Nice to know it works as you want.

Best regards

villy · Aug 9, 2011

Code:

Sub transpose()
last = Cells(Rows.Count, "C").End(xlUp).Row
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lr
    With WorksheetFunction.Application
        If Not IsError(.Index(Range("C2:C" & last), _
            .Match(Range("A" & i), Range("C2:C" & last), 0), 1)) = True Then
            Set D_Rng = Range("D" & .Match(Range("A" & i), Range("C2:C" & last), 0) + 1)
            D_Rng.Value = D_Rng & "--" & Range("B" & i)
        Else
            Range("C" & last + 1).Value = Range("A" & i)
            Range("D" & last + 1).Value = Range("B" & i)
            last = last + 1
        End If
    End With
Next
Columns("A:B").Delete Shift:=xlToLeft
End Sub

Help with transpose

SeanDamnit

Board Regular

Excel Facts

cgcamal

Active Member

villy

Active Member

MrVillareal

Well-known Member

SeanDamnit

Board Regular

MrVillareal

Well-known Member

SeanDamnit

Board Regular

MrVillareal

Well-known Member

cgcamal

Active Member

villy

Active Member

Similar threads

Forum statistics

Share this page

Help with transpose

Board Regular

Excel Facts

Active Member

Active Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Active Member

Active Member

Similar threads

Forum statistics

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