jmoriarty
asked on
excel macro to find and sum multiple column values
Hey folks,
I have an excel sheet with 4 columns. I'm trying to aggregate the data in the SKU columns, with the data in the quantity columns, for each sku.
Basically, I'm trying to find multiple instances of each sku, add their respective quantities, to give me an aggregate total grouped by sku. See attached example for more info.
What I'm doing now is going through and assigning each sku a color, then manually adding each quantity to a aggregated sum shown on the right, but is there a faster way to do this via vb script or similar?
In lieu of that, I was thinking of using a PHP script to upload all data to a mysql data, group and sum accordingly, then export back out the manipulated set, but a vba solution would be much faster I'm thinking.
Any input greatly appreciated.
products1.xls
I have an excel sheet with 4 columns. I'm trying to aggregate the data in the SKU columns, with the data in the quantity columns, for each sku.
Basically, I'm trying to find multiple instances of each sku, add their respective quantities, to give me an aggregate total grouped by sku. See attached example for more info.
What I'm doing now is going through and assigning each sku a color, then manually adding each quantity to a aggregated sum shown on the right, but is there a faster way to do this via vb script or similar?
In lieu of that, I was thinking of using a PHP script to upload all data to a mysql data, group and sum accordingly, then export back out the manipulated set, but a vba solution would be much faster I'm thinking.
Any input greatly appreciated.
products1.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks matthewspatrick,
Yes, I should use SUMIF instead, I always use SUMPRODUCT because it allow me to input multi criteria so I forget the simple SUMIF can do the same thing.
I totally agree PivotTable approach dynamically finds the different SKUs is good.
But using formula allow the anwser store in a cell, which is useful when we need print it in specific format, or use this result as a part of other formula.
Yes, I should use SUMIF instead, I always use SUMPRODUCT because it allow me to input multi criteria so I forget the simple SUMIF can do the same thing.
I totally agree PivotTable approach dynamically finds the different SKUs is good.
But using formula allow the anwser store in a cell, which is useful when we need print it in specific format, or use this result as a part of other formula.
// Example formula in F2: (assume the last row number of the data is 300)
=SUMIF($A$2:$G$300,UPPER(J2),$B$2:$H$300)
ASKER
Hey folks,
Sorry for the long delay in reply; just getting around to working on this again. the pivot table is exactly what I was looking for since I'm not always sure of the range of values I'm working with. One further question though: how do you sort the results (re: the count of values) once you have the pivot table set?
Thanks again.
Sorry for the long delay in reply; just getting around to working on this again. the pivot table is exactly what I was looking for since I'm not always sure of the range of values I'm working with. One further question though: how do you sort the results (re: the count of values) once you have the pivot table set?
Thanks again.
ASKER
Nevermind, got it.
Thanks again!
Thanks again!
Any particular reason why you are recommendation SUMPRODUCT or array formulae when a simple
SUMIF would do quite nicely?
:)
Anyway, I do recommend the PivotTable approach because it dynamically finds the different SKUs,
whereas a formula-based approach presupposes that you already have a list of the SKUs you want
to analyze.
Regards,
Patrick