Roll up data in Excel?

  • Thread starter Thread starter Kenneth E. Coakley
  • Start date Start date
K

Kenneth E. Coakley

I have a sales file in Excel with the following structure:

SKU $Sold UnitsSold
107 5 2
107 8 3
148 10 4
288 2 7
288 5 9
288 5 9
288 6 9
etc...

A given SKU can have any number of rows associated with it. How can I easily
roll this file to one line per SKU, as below?

SKU $Sold UnitsSold
107 13 5
148 10 4
288 18 34
etc...

Thanks for any help!

Ken
 
Have you considered using a PivotTable?

Highlight your data and then: DATA|PivotTable and PivotChart Report

a wizard will walk you through it.

HTH,

ryanb.
 
I like the pivottable approach, but if your SKU numbers are sorted, you could
select your range and do:

Data|Subtotal

Then use the outlining symbols to the left to hide/show the details.
 
Ryan & Dave...

Thanks for your suggestions.

Originally I had tried using PivotTables but found it to be a bit ungainly
with the amount of data I'm working with. Additionally, my end goal is to
import the summarized data into SQL Server. And, honestly, I don't know how
well that works with a PivotTable.

I attempted to use the Data/Subtotal method. That seemed to work easily at
first, but I was unable to copy and paste just the summarized data.

So I went back to PivotTables, spent some more time getting the structure
correct and then copied and pasted that data to a new sheet. I had hoped to
find a simpler method since this is something I do monthly, but at least
it's working now.

Thank you both for your help.

Ken
 
I still like the pivottable suggestion (and with a little learning, it'll be
quicker).

But after you do data|subtotals, you can use the outlining symbols at the left.
Then select the range.

Now hit F5 (or edit|goto), click special, click Visible cells only.

Copy and paste that subset of data.
 
Oh, great tip. Thanks for the suggestion, Dave.

I've played around with the PivotTables some more and they do seem workable
as a solution.

Thanks again,
Ken
 
Ok, now you've done it. You've expressed an interest in pivottables!

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx



Kenneth E. Coakley said:
Oh, great tip. Thanks for the suggestion, Dave.

I've played around with the PivotTables some more and they do seem workable
as a solution.

Thanks again,
Ken
Click to expand...
 
Back
Top