Excel Re-arranging data - row to column

[gituncar]

Hi, I have this data on excel

SCALE_NO	SCALE_NAME	SIZ
1​	DUAL	3942
1​	DUAL	4346
1​	DUAL	L/XL
1​	DUAL	M/L
1​	DUAL	S/M
1​	DUAL	XS/S
2​	ONE SIZE	O/S
3​	TALLA	L
3​	TALLA	M
3​	TALLA	S
3​	TALLA	XL
3​	TALLA	XS
3​	TALLA	XXL
3​	TALLA	XXS

if possible rearrange with some steps like that

SCALE_NO	SCALE_NAME	SIZ
1​	DUAL	3942	4346	L/XL	M/L	S/M	XS/S
2​	ONE SIZE	O/S
3​	TALLA	L	M	S	XL	XS	XXL	XXS

 

[sandy666]

you can try Power Query

SCALE_NO	SCALE_NAME	SIZ		SCALE_NO	SCALE_NAME	SIZ.1	SIZ.2	SIZ.3	SIZ.4	SIZ.5	SIZ.6	SIZ.7
1	DUAL	3942		1	DUAL	3942	4346	L/XL	M/L	S/M	XS/S
1	DUAL	4346		2	ONE SIZE	O/S
1	DUAL	L/XL		3	TALLA	L	M	S	XL	XS	XXL	XXS
1	DUAL	M/L
1	DUAL	S/M
1	DUAL	XS/S
2	ONE SIZE	O/S
3	TALLA	L
3	TALLA	M
3	TALLA	S
3	TALLA	XL
3	TALLA	XS
3	TALLA	XXL
3	TALLA	XXS

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"SCALE_NO", "SCALE_NAME"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "SIZ", each [Count][SIZ]),
    Extract = Table.TransformColumns(List, {"SIZ", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "SIZ", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"SIZ.1", "SIZ.2", "SIZ.3", "SIZ.4", "SIZ.5", "SIZ.6", "SIZ.7"})
in
    Split

 

[Fluff]

How about

Sub gituncar()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value & "|"
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & Cl.Offset(, 2).Value & "|"
         End If
      Next Cl
      Range("E2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .items))
      Range("F2", Range("F" & Rows.Count).End(xlUp)).TextToColumns Range("F2"), xlDelimited, , , 0, 0, 0, 0, 1, "|"
   End With
End Sub

 

[gituncar]

Let me test it. Thanks

 

[Fluff]

Ok