Formula or vba that will split by unique value in column A, then transpose all values with same value in column A in columns b- etc

mnty89 · Sep 22, 2017

Kind of hard to explain in a title but easy to show with sample data.. Basically I will have a series of “ID” values in column A, those will repeat will associated unique values per that ID in column B. I want to format that into a one liner, so that for all of the unique values associated with that ID, it will get pasted in a sort of transposed version in cells B- however many values there are for the ID.

Before and after are below..

Before

ID	Values
11111	abc
11111	def
11111	ghi
11111	jkl
2222	abc
2222	def
2222	ghi
2222	jkl
2222	mno

After

[TABLE="width: 384"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Values
[/TD]
[TD]Values
[/TD]
[TD]Values
[/TD]
[TD]Values
[/TD]
[TD]Values
[/TD]
[/TR]
[TR]
[TD]11111
[/TD]
[TD]abc
[/TD]
[TD]def
[/TD]
[TD]ghi
[/TD]
[TD]jkl
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2222
[/TD]
[TD]abc
[/TD]
[TD]def
[/TD]
[TD]ghi
[/TD]
[TD]jkl
[/TD]
[TD]mno
[/TD]
[/TR]
</tbody>[/TABLE]

Joe4 · Sep 22, 2017

Here is one way:

Code:

Sub MyTranspose()

    Dim myRow As Long
        
    Application.ScreenUpdating = False
    
    myRow = 2
   
    Do Until Cells(myRow, "A") = ""
        If Cells(myRow, "A") = Cells(myRow - 1, "A") Then
            Cells(myRow - 1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(myRow, "B")
            Rows(myRow).Delete
        Else
            myRow = myRow + 1
        End If
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Eric W · Sep 22, 2017

Here's a formula method:

	A	B	C	D	E
ID	Values	ID	Values
abc	abc	def	ghi	jkl
def	abc	def	ghi	jkl	mno
ghi	x	y
jkl
abc
def
ghi
jkl
mno
x
x
y

<colgroup><col style="width: 25pxpx"><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: center"]2[/TD]
[TD="align: right"]11111[/TD]

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

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

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

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

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

[TD="align: center"]5[/TD]
[TD="align: right"]11111[/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"]6[/TD]
[TD="align: right"]2222[/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"]7[/TD]
[TD="align: right"]2222[/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"]8[/TD]
[TD="align: right"]2222[/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"]9[/TD]
[TD="align: right"]2222[/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"]10[/TD]
[TD="align: right"]2222[/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"]11[/TD]
[TD="align: right"]444[/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"]12[/TD]
[TD="align: right"]444[/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"]13[/TD]
[TD="align: right"]444[/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"]14[/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]

</tbody>

Sheet12

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IF(D1="","",IFERROR(INDEX($A$2:$A$20,SMALL(IF($A$2:$A$20<>"",IF(COUNTIF($D$1:$D1,$A$2:$A$20)=0,ROW($A$2:$A$20)-ROW($A$2)+1)),1)),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IF(D2="","",IFERROR(INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20=$D2,IF($B$2:$B$20<>"",IF(COUNTIF($D2:D2,$B$2:$B$20)=0,ROW($B$2:$B$20)-ROW($B$2)+1))),1)),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put the D2 formula in, change the references to match your sheet, confirm with Control+Shift+Enter, then drag down as far as needed. Put the E2 formula in the same way, then drag down and to the right as far as needed. The D1:E1 headers are required.

mnty89 · Sep 22, 2017

both worked perfectly thanks!

Nowherefast · Feb 14, 2018

Hello...Sorry to hijack this thread...This code works for what I need, but now I have another file that I need to transpose and this file has 23 columns...Where would I make a change to the code above (that worked perfectly for my file that only had 2 columns)...I need to do the same to the other files that has 23 columns...I'm not sure where to make the range change...

Using this code as mentioned in a post above...

Sub MyTranspose()

Dim myRow As Long

Application.ScreenUpdating = False

myRow = 2

Do Until Cells(myRow, "A") = ""
If Cells(myRow, "A") = Cells(myRow - 1, "A") Then
Cells(myRow - 1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(myRow, "B")
Rows(myRow).Delete
Else
myRow = myRow + 1
End If
Loop

Application.ScreenUpdating = True

End Sub
Thank you

Fazza · Feb 14, 2018

To not hijack the thread, please change your source data to 2 columns instead of 23. OK?

Otherwise please start a new thread (& include a link to this one if you want/need to).

regards

Fazza · Feb 14, 2018

Maybe a cross-tab query could be used, too. No formulas & no VBA. Could be like

Code:

TRANSFORM MAX(Values)
SELECT ID
FROM [YourSourceDataTableName]
GROUP BY  ID
PIVOT Values

Formula or vba that will split by unique value in column A, then transpose all values with same value in column A in columns b- etc

mnty89

Board Regular

Joe4

MrExcel MVP, Junior Admin

Eric W

MrExcel MVP

mnty89

Board Regular

Nowherefast

New Member

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Similar threads

Share this page

Formula or vba that will split by unique value in column A, then transpose all values with same value in column A in columns b- etc

mnty89

Board Regular

Joe4

MrExcel MVP, Junior Admin

Eric W

MrExcel MVP

mnty89

Board Regular

Nowherefast

New Member

Fazza

MrExcel MVP

Fazza

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