VBA code to shrink the rows by cumilating the data

[Wafee]

I have a excel sheet with around 5lakh rows and i want to reduce number of rows by cumilating same data.
below is the sample data (Couldn't share original data due to privacy reasons). Data should be combined based on Columns B,D,E,G.

If data present in B,D,E,F columns is same the code has to sum the values in G column and make it in to single row of data as shown in the next table.
Original data is not sorted and output expected is given below.
Would be great if Output can be obtained be in a new sheet in the same workbook leaving orinal data as it is.

A	B	C	D	E	F	G
City Code	City Name	Pincode	Date	Type	Description	Total
1	NewYork	32	1-Jan-20	SA	on	40
1	NewYork	32	1-Jan-20	SA	on	40
1	NewYork	32	1-Jan-20	NA	off	20
2	Sydney	44	20-Jan-20	NA	off	40
2	Sydney	44	20-Jan-20	NA	off	40
3	Delhi	94	20-Jan-20	SA	on	20
3	Delhi	94	20-Jan-20	SA	on	20
3	Delhi	94	20-Jan-20	SA	on	40

Out put expected is below.

City Code	City Name	Pincode	Date	Type	Description	Total
1	NewYork	32	1-Jan-20	SA	on	80
1	NewYork	32	1-Jan-20	NA	off	20
2	Sydney	44	20-Jan-20	NA	off	80
3	Delhi	94	20-Jan-20	SA	on	80

 

[alansidman]

Using Power Query which you can download from MS as an add in for your version of Excel. ( It is native to 2016 and later).

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City Code", Int64.Type}, {"City Name", type text}, {"Pincode", Int64.Type}, {"Date", type date}, {"Type", type text}, {"Description", type text}, {"Total", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City Name", "Date", "Type", "Description"}, {{"Total", each List.Sum([Total]), type nullable number}})
in
    #"Grouped Rows"

Book2
	A	B	C	D	E
1	City Name	Date	Type	Description	Total
2	NewYork	1/1/2020	SA	on	80
3	NewYork	1/1/2020	NA	off	20
4	Sydney	1/20/2020	NA	off	80
5	Delhi	1/20/2020	SA	on	80
Sheet2

 

[Wafee]

Using Power Query which you can download from MS as an add in for your version of Excel. ( It is native to 2016 and later).

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City Code", Int64.Type}, {"City Name", type text}, {"Pincode", Int64.Type}, {"Date", type date}, {"Type", type text}, {"Description", type text}, {"Total", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City Name", "Date", "Type", "Description"}, {{"Total", each List.Sum([Total]), type nullable number}})
in
    #"Grouped Rows"

Book2
	A	B	C	D	E
1	City Name	Date	Type	Description	Total
2	NewYork	1/1/2020	SA	on	80
3	NewYork	1/1/2020	NA	off	20
4	Sydney	1/20/2020	NA	off	80
5	Delhi	1/20/2020	SA	on	80
Sheet2

Hi Alan,

I have installed power query now. can you help on how to run above code? I tried to run it in VB editor but it was not possible.
My workbook name is "wb1" and data is in the sheet named "Source" also have an empty sheet named "output" for the output.

 

[alansidman]

What is Power Query?

Power Query description

www.poweredsolutions.co

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions