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]

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

<tbody>
</tbody>

After

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

<tbody>
</tbody>

 

[Joe4]

Here is one way:

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]

Here's a formula method:

	A	B	C	D	E	F	G	H	I
1	ID	Values		ID	Values
2	11111	abc		11111	abc	def	ghi	jkl
3	11111	def		2222	abc	def	ghi	jkl	mno
4	11111	ghi		444	x	y
5	11111	jkl
6	2222	abc
7	2222	def
8	2222	ghi
9	2222	jkl
10	2222	mno
11	444	x
12	444	x
13	444	y
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula D2 {=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)),""))} E2 {=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)),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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]

both worked perfectly thanks!

 

[Nowherefast]

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]

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]

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

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