VBA for subtotal or is there another easier way?

[cytochrome]

Hi there,
I have a spreadsheet looked like below.

CODE	Account	Paydate	Net		Gross	Currency
123​	ABC	20221216​	1​	​	5​	USD
360206​	DEF	20221217​	2​	​	10​	CAD
123​	ABC	20221216​	10​	​	15​	USD
360206​	GHI	20221216​	20​	​	20​	USD

I dont know if VBA is better or using existing functions in Excel. But im looking to total the Net and Gross column if CODE, Account, Paydate, Currency are the same. So should look something like below. The new table should be on another sheet. Any help is greatly appreaciated.

CODE	Account	Paydate	Net		Gross	Currency
123​	ABC	20221216​	11​	​	20​	USD
360206​	DEF	20221217​	2​	​	10​	CAD
360206​	GHI	20221216​	20​	​	20​	USD
​		​	​	​	​

 

[James006]

Hi,
Have you tried to insert a Pivot Table ?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cytochrome]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Just update. I'm using Excel 365.

 

[cytochrome]

I thought of using Pivot Table but the resulting data doesn't look that clean. Plus my actual spreadsheet has 100,000+ lines on it so it takes awhile for Pivot Table to update.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	CODE	Account	Paydate	Net		Gross	Currency
2	123	ABC	20221216	1		5	USD
3	360206	DEF	20221217	2		10	CAD
4	123	ABC	20221216	10		15	USD
5	360206	GHI	20221216	20		20	USD
6
Sheet1

Fluff.xlsm
	A	B	C	D	E	F	G
1	CODE	Account	Paydate	Net		Gross	Currency
2	123	ABC	20221216	11		20	USD
3	360206	DEF	20221217	2		10	CAD
4	360206	GHI	20221216	20		20	USD
5
Sheet2

Cell Formulas
Range	Formula
A2:G4	A2	=LET(u,UNIQUE(CHOOSECOLS(FILTER(Sheet1!A2:G500,Sheet1!A2:A500<>""),1,2,3,7)),HSTACK(TAKE(u,,3),SUMIFS(Sheet1!D:D,Sheet1!A:A,INDEX(u,,1),Sheet1!B:B,INDEX(u,,2),Sheet1!C:C,INDEX(u,,3),Sheet1!G:G,INDEX(u,,4)),EXPAND("",ROWS(u),,""),SUMIFS(Sheet1!F:F,Sheet1!A:A,INDEX(u,,1),Sheet1!B:B,INDEX(u,,2),Sheet1!C:C,INDEX(u,,3),Sheet1!G:G,INDEX(u,,4)),TAKE(u,,-1)))
Dynamic array formulas.

 

[cytochrome]

Wow that works! AMAZING! Thank you!!!!!
I'm guessing the formula will work for 500 rows of data on Sheet2 right? And I'm also guessing your logic for the solution is Dynamic Array? If so then i will definitely need to learn more about it as it's some neat stuff.

Again thank you for your help.

 

[Fluff]

I'm guessing the formula will work for 500 rows of data on Sheet2 right?

It can work for more, you just need to change the ranges.

I'm also guessing your logic for the solution is Dynamic Array?

That's right.