Transpose data from rows to columns

[yxz152830]

Gurus,
I have a data set that looks like this:

A	1​
A	2​
A	3​
B	1​
B	2​
C	1​
C	2​
C	3​
C	4​

I'm hoping to convert the set to this:

A	1​	2​	3​
B	1​	2​
C	1​	2​	3​	4​

is there a way to do it without VBA?
Thanks in advance!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1		A	1	2	3
3	A	2		B	1	2
4	A	3		C	1	2	3	4
5	B	1
6	B	2
7	C	1
8	C	2
9	C	3
10	C	4
Sheet6

Cell Formulas
Range	Formula
D2:H4	D2	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,TOROW(EXPAND(FILTER(B2:B100,A2:A100=y),m,,"")))))),1))
Dynamic array formulas.

 

[yxz152830]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1		A	1	2	3
3	A	2		B	1	2
4	A	3		C	1	2	3	4
5	B	1
6	B	2
7	C	1
8	C	2
9	C	3
10	C	4
Sheet6

Cell Formulas
Range	Formula
D2:H4	D2	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,TOROW(EXPAND(FILTER(B2:B100,A2:A100=y),m,,"")))))),1))
Dynamic array formulas.

this formula is wayy too complex i rather VBA it. Is pivot table or something gonna work at all?

 

[Fluff]

If you want VBA, why did you ask for a formula?
You will have to wait for someone else to suggest a macro.

 

[Fluff]

Another option with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1		A	1	2	3
3	A	2		B	1	2
4	A	3		C	1	2	3	4
5	B	1
6	B	2
7	C	1
8	C	2
9	C	3
10	C	4
Sheet6

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A10)
E2:G2,E4:H4,E3:F3	E2	=TOROW(FILTER($B$2:$B$10,$A$2:$A$10=D2))
Dynamic array formulas.

 

[alansidman]

An alternative to VBA is Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table [Column1=text, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"INDEX",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column2", "INDEX"}, {"Column2", "INDEX"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"INDEX", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"INDEX", type text}}, "en-US")[INDEX]), "INDEX", "Column2")
in
    #"Pivoted Column"

 

[jolivanes]

Been waiting to see what OP wants. Not many solutions if you don't want a macro and formulae are too complex.
It's pretty simple with a macro.

 

[yxz152830]

Another option with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1		A	1	2	3
3	A	2		B	1	2
4	A	3		C	1	2	3	4
5	B	1
6	B	2
7	C	1
8	C	2
9	C	3
10	C	4
Sheet6

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A10)
E2:G2,E4:H4,E3:F3	E2	=TOROW(FILTER($B$2:$B$10,$A$2:$A$10=D2))
Dynamic array formulas.

this one is simple enough but the transposed data has too many irrelavent data. Is there a way to let the filter formula to filter the specified columns to return and then transpose using torow?
Thanks~!

 

[Fluff]

I don't understand what you mean. The formula produces what you said you wanted.

 

[alex78]

with macro...

Sub test()

Set d = CreateObject("Scripting.Dictionary"): Application.ScreenUpdating = False


With Sheets("Sheet1")
Dim table(): table = .Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value

Dim i As Long: For i = LBound(table) To UBound(table)
d(table(i, 1)) = d(table(i, 1)) & table(i, 2) & ","
Next i

.Range("D1").Resize(d.Count) = Application.Transpose(d.keys)
.Range("E1").Resize(d.Count) = Application.Transpose(d.items)
.Range("E1").Resize(d.Count).TextToColumns comma:=True

End With

Application.ScreenUpdating = True: Set d = Nothing: Erase table
End Sub