Moving 2 column into rows, when data from first column should be grouped

[paulusek]

How to move data from 2 columns into matrix, when data from 1st column should be grouped? I do know have any power tools, basic XLS only.
Thanks a lot for support!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	11	101
2	11	102			11	12	13	14	15
3	11	103			101	109	113	115	117
4	11	104			102	110	114	116	118
5	11	105			103	111			119
6	11	106			104	112			120
7	11	107			105				121
8	11	108			106				122
9	12	109			107				123
10	12	110			108
11	12	111
12	12	112
13	13	113
14	13	114
15	14	115
16	14	116
17	15	117
18	15	118
19	15	119
20	15	120
21	15	121
22	15	122
23	15	123
24
Master

Cell Formulas
Range	Formula
E2:I10	E2	=LET(u,UNIQUE(FILTER(A1:A40,A1:A40<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,HSTACK(x,EXPAND(VSTACK(y,TOCOL(FILTER(B1:B40,A1:A40=y))),m+1,,"")))),,1))
Dynamic array formulas.

 

[alansidman]

As an alternative you could do the same with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table [Column1=number, 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", {{"Column1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Column1", type text}}, "en-US")[Column1]), "Column1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"