Roll up data in Excel?

[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

 

[ryanb.]

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.

 

[Dave Peterson]

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.

 

[Kenneth E. Coakley]

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

 

[Dave Peterson]

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.

 

[Kenneth E. Coakley]

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

 

[Dave Peterson]

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

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