VBA to summarize data?

[Holley]

Hello all!

I am looking for a way to summarize numerous rows of data. It could possibly exceed 100k rows. For example, column A is a reference #. I need to count how many times this row is listed. Column H is an amount and I need for it to subtotal each reference #. For example Row A is 1234 and there are 100 instance that total $50000. I need this to happen at each change in column A and, if possible, copy the data to a new spreadsheet.
Would this be possible with VBA?

Thanks in advance!!

 

[Joe4]

I don't think you need VBA to do it.
Sounds like something you should be able to do with Pivot Tables, Subtotals, or Power Query.
If it was me, with that much data I would dump it into a relational database program like Microsoft Access where grouping, counting, totalling, and reporting on data becomes much easier, especially with large amounts of data. But you should be able to do similar functions in Power Query in Excel.

 

[Bosquedeguate]

from your description it I think your data is sorted by the reference number. You stated that "this needs to happen at each change in Column A" (that suggests to me that your data is sorted).
If it is sorted then the VBA code to total at each change in the reference number is pretty easy.

 

[Joe4]

If it is sorted then the VBA code to total at each change in the reference number is pretty easy.

Isn't that what inserting Subtotals already does?
No sense in recreating the wheel...

 

[Holley]

Isn't that what inserting Subtotals already does?
No sense in recreating the wheel...

True, but I only need the summarized data and I was hoping VBA may accomplish this

 

[Holley]

from your description it I think your data is sorted by the reference number. You stated that "this needs to happen at each change in Column A" (that suggests to me that your data is sorted).
If it is sorted then the VBA code to total at each change in the reference number is pretty easy.

Yes, this is sorted. I have tried using Subtotals, but I need these on the same row, not subtotal in one row and count on another if possible.

 

[Joe4]

I think it will probably be necessary for you to show us some sample data and your expected output, as with VBA, so much is dependent on your actual data layout.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Holley]

I think it will probably be necessary for you to show us some sample data and your expected output, as with VBA, so much is dependent on your actual data layout.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Totally understandable! This is an example of how the data is in the spreadsheet

Sample 1.xlsx
	A	B	C	D	E	F	G	H	I
1	V#	Vname	C#	Cname	queue	I Date	I#	I$	Ddate
2	0234	Ralph Lauren	1234567	Susie Jones	1A	7/6/2023	123	$ 121.00	8/5/2023
3	0234	Ralph Lauren	1234567	Susie Jones	1A	7/7/2023	124	$ 1,258.55	8/6/2023
4	0234	Ralph Lauren	1234567	Susie Jones	1A	7/7/2023	125	$ 578.58	8/6/2023
5	1234	Equestrian	1234567	Susie Jones	1A	6/1/2023	456	$ 878.88	7/1/2023
6	1234	Equestrian	1234567	Susie Jones	1A	6/5/2023	457	$ 4,875.00	7/5/2023
7	1234	Equestrian	1234567	Susie Jones	1A	6/15/2023	458	$ 78.00	7/15/2023
8	1234	Equestrian	1234567	Susie Jones	1A	7/1/2023	459	$ 81.00	7/31/2023
9	5678	MAC	1234567	Susie Jones	1A	8/1/2023	12	$ 108.00	8/31/2023
10	5678	MAC	1234567	Susie Jones	1A	8/1/2023	13	$ 126.00	8/31/2023
Sheet1

And this is how I need the data to be summarized (preferably in a new spreadsheet, but new tab if not possible)

Desired Output.xlsx
	A	B	C	D	E	F	G
1	V#	Vname	C#	Cname	queue	# of I	Tally
2	0234	Ralph Lauen	1234567	Susie Jones	1A	3	$ 1,958.13
3	1234	Equestrian	1234567	Susie Jones	1A	4	$ 5,912.88
4	5678	MAC	1234567	Susie Jones	1A	2	$ 234.00
5	Grand Total					9	$ 8,105.01
Sheet1

Cell Formulas
Range	Formula
F5:G5	F5	=SUM(F2:F4)

 

[Joe4]

For your Office version, you have listed both 365 and 2016.
Which one are you currently using?
365 has a bunch of new functions that 2016 does not.

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"V#", Int64.Type}, {"Vname", type text}, {"C#", Int64.Type}, {"Cname", type text}, {"queue", type text}, {"I Date", type date}, {"I#", Int64.Type}, {" I$ ", type number}, {"Ddate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"V#", "Vname", "C#", "Cname", "queue"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Sum", each List.Sum([#" I$ "]), type nullable number}})
in
    #"Grouped Rows"