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!!

 

[Holley]

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"

I hate to admit this, but I am not familiar with Power Query. How would I execute this?

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Holley]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

THANK YOU!!! So glad its built in as we cannot use if has to be added manually.

 

[Holley]

I hate to admit this, but I am not familiar with Power Query. How would I execute this?

Getting familiar with Power Query and this is an awesome piece that I can see lots of uses for once I'm more familiar. I've entered the code above and all is working great except the sum in column I$. There is an error in the Sum column and the column I$ is not in the table. How can I ensure it is capturing this column?

 

[Holley]

Getting familiar with Power Query and this is an awesome piece that I can see lots of uses for once I'm more familiar. I've entered the code above and all is working great except the sum in column I$. There is an error in the Sum column and the column I$ is not in the table. How can I ensure it is capturing this column? View attachment 98279View attachment 98281

Found the problem and fixed it. The data did not import properly in that column. Once I got that fixed, it was EXACTLY what I needed and wanted. Thank you so much!!