Level up the data

[vcheung]

Hi,

I have a set of sales data with customer name, invoice date, account code, etc., and I want to have your help how to level up this set of data to group the sales data by account code for each customer name to have another set of data?

For example as below.

BEFORE
Date	Customer Name	Account code	Region	Amount
1/1/2021​	ABC Ltd.	60100	North	600​
1/1/2021​	ABC Ltd.	60100	South	300​
15/1/2021​	DEF Ltd.	61000	North	1200​
10/2/2021​	ABC Ltd.	60100	South	500​
28/2/2021​	DEF Ltd.	61000	North	20​
	AFTER
	Customer Name	Account code	Region	Amount
	ABC Ltd	60100​	North	600​
	ABC Ltd	60100​	South	800​
	DEF Ltd.	61000​	North	1220​

Best Regards

V. Cheung

 

[bebo021999]

Try post sample with XL2BB in order to get cell address of data BEFORE and AFTER

 

[vcheung]

Try post sample with XL2BB in order to get cell address of data BEFORE and AFTER

Hi, Please advise what is XL2BB?

Best Regards

V. Cheung

 

[bebo021999]

In edit window, on top-right menu, you can see XL2BB menu, click to download and run it.

 

[alansidman]

A Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Name", "Account code", "Region"}, {{"Total", each List.Sum([Amount]), type any}})
in
    #"Grouped Rows"

Book3
	A	B	C	D	E	F	G	H	I	J
1	Date	Customer Name	Account code	Region	Amount		Customer Name	Account code	Region	Total
2	1/1/2021	ABC Ltd.	60100	North	600.00		ABC Ltd.	60100	North	600
3	1/1/2021	ABC Ltd.	60100	South	300.00		ABC Ltd.	60100	South	800
4	1/15/2021	DEF Ltd.	61000	North	1200.00		DEF Ltd.	61000	North	1220
5	2/10/2021	ABC Ltd.	60100	South	500.00
6	2/28/2021	DEF Ltd.	61000	North	20.00
Sheet1

 

[mohadin]

Hi
May try

Sub test()
Dim i As Long
Dim a As Variant
a = Cells(1, 1).CurrentRegion
 With CreateObject("scripting.dictionary")
  For i = 1 To UBound(a)
  t = a(i, 2) & a(i, 4)
    If Not .Exists(t) Then
    .Add t, Array(a(i, 2), a(i, 3), a(i, 4), a(i, 5))
    Else
  x = .Item(t):  x(3) = x(3) + a(i, 5):  .Item(t) = x
    End If
   Next
    Cells(1, 8).Resize(.Count, 4) = Application.Transpose(Application.Transpose(.items))
  End With
End Sub