Sub Rearrange()
Dim a As Variant, b As Variant
Dim i As Long, r As Long, c As Long, MaxCols As Long
a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 2)
For i = 2 To UBound(a)
If a(i, 1) <> a(i - 1, 1) Then
r = r + 1
b(r, 1) = a(i, 1)
b(r, 2) = a(i, 2)
c = 2
Else
c = c + 1
If c > MaxCols Then
MaxCols = c
ReDim Preserve b(1 To UBound(b), 1 To MaxCols)
End If
b(r, c) = a(i, 2)
End If
Next i
With Range("E1").Resize(, MaxCols)
.Formula = "=""Invoice ""&COLUMNS($E1:E1)-1"
.Value = .Value
.Cells(1).ClearContents
.Offset(1).Resize(r).Value = b
.EntireColumn.AutoFit
End With
End Sub
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Invoice", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Data", each _, type table [Customer=text, Invoice=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Invoice", "Index"}, {"Custom.Invoice", "Custom.Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Invoice", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Invoice1"}, {"2", "Invoice2"}, {"3", "Invoice3"}})
in
#"Renamed Columns"
Book2 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Customer | Invoice1 | Invoice2 | Invoice3 | ||
2 | Customer1 | 55 | ||||
3 | customer2 | 56 | 57 | 58 | ||
4 | customer3 | 59 | ||||
5 | customer4 | 60 | 61 | |||
Sheet2 |
Customer | Invoice1 | Invoice2 | Invoice3 | 4 | 5 |
Customer 1 | 55 | ||||
Customer 2 | 56 | 57 | 58 | ||
Customer 3 | 59 | ||||
Customer 4 | 60 | 61 | |||
Customer 5 | 100 | 120 | 130 | 140 | 150 |
Customer | Invoice.1 | Invoice.2 | Invoice.3 | Invoice.4 | Invoice.5 |
Customer 1 | 55 | ||||
Customer 2 | 56 | 57 | 58 | ||
Customer 3 | 59 | ||||
Customer 4 | 60 | 61 | |||
Customer 5 | 100 | 120 | 130 | 140 | 150 |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Customer"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Invoice", each [Count][Invoice]),
Extract = Table.TransformColumns(List, {"Invoice", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
SortLen = Table.Sort(Table.AddColumn(Extract, "Length", each Text.Length([Invoice]), Int64.Type),{{"Length", Order.Descending}}),
Split = Table.SplitColumn(SortLen, "Invoice", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
Sort = Table.Sort(Table.RemoveColumns(Split,{"Count", "Length"}),{{"Customer", Order.Ascending}})
in
Sort
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Data", each _, type table [Customer=text, Invoice=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Invoice", "Index"}, {"Custom.Invoice", "Custom.Index"}),
#"Added Prefix" = Table.TransformColumns(#"Expanded Custom", {{"Custom.Index", each "Invoice " & Text.From(_, "en-GB"), type text}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Prefix", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Prefix", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Invoice", List.Sum)
in
#"Pivoted Column"