Solved

excel macro to find and sum multiple column values

Posted on 2009-07-01
7
1,054 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:jmoriarty
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 400 total points
ID: 24760525
Hello jmoriarty,

No need for code.  I was able to build a PivotTable quite easily.  The following work steps are for Excel 2003:

1) Select Data|PivotTable & PivotChart Report from the menu

2) In Step 1 of the wizard, select multiple consolidation ranges as the source

3) In Step 2a, select 'create single page field'

4) In Step 2b, I selected A1:B74 and clicked add, and continued for the other blocks of SKU data

5) In Step 3, I clicked finish

That gave me a PivotTable that aggregated the quantities by SKU.

Regards,

Patrick
0
 
LVL 5

Assisted Solution

by:yauhing
yauhing earned 50 total points
ID: 24761142
You can use excel formula to do it, please find attached.
(some result are not same as yours because the typo in your sheet)

For example: input below formula in the Cell "L2" can get the same result.

=SUMPRODUCT(($A$2:$A$300 = J2) * $B$2:$B$300) + SUMPRODUCT(($C$2:$C$300 = J2) * $D$2:$D$300) + SUMPRODUCT(($E$2:$E$300 = J2) * $F$2:$F$300) + SUMPRODUCT(($G$2:$G$300 = J2) * $H$2:$H$300)
products1.xls
0
 
LVL 1

Assisted Solution

by:kiranjjain
kiranjjain earned 50 total points
ID: 24762283
use sum(if()) array function as shown in thefile attached. anyways i have done all the calculations for u if that was the only thing required. no need for VB Script

Regards,
Kiran Jain
products1.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24762763
yauhing and kiranjjain,

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
0
 
LVL 5

Expert Comment

by:yauhing
ID: 24769343
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.
// 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)

Open in new window

0
 

Author Comment

by:jmoriarty
ID: 24817748
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.
0
 

Author Comment

by:jmoriarty
ID: 24822001
Nevermind, got it.

Thanks again!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now