Subtotal macro and delete original data

[ExcelNovice]

I am trying to subtotal the following data:

Location ProductID Qty
1 2a 5
1 2a 2
1 2a 10
32 4bc 1
32 4bc 4
32 r3s 8


I want the end result to look like this (I do not want the word "subtotal"
anywhere on my worksheet):
Location ProductID Qty
1 2a 17
32 4bc 5
32 r3s 8

and delete the original data rows so the subtotaled rows are the only rows
in the worksheet.
(I am using Excel 2003 and need all the rows I can get)

Any help is greatly appreciated.

 

[Dave Peterson]

If I only had to do this once, I'd do the data|subtotal stuff
Then hide the details and copy the visible rows to a new worksheet and paste as
values

Then do an edit|replace to replace the Total (or Average or....) with (nothing).

If I had to do this lots of times, I'd learn about Data|pivottable.

Then convert that pivottable to values (select it, copy|pastespecial|values)

And if I had to do it lots and lots of times, I'd record a macro when I did the
pivottable stuff.


If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/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

 

[Joel]

Sub combinerows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = _
Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = _
Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = _
Range("C" & RowCount) + _
Range("C" & (RowCount + 1))

Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub

 

[Jim Cone]

Try recording a macro while applying subtotals to your data.
I did and got this (header in row 5) ...
'--
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Rows("15:15").Select
Selection.Delete Shift:=xlUp
Range("B5").Select
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"ExcelNovice"
wrote in message
I am trying to subtotal the following data:

Location ProductID Qty
1 2a 5
1 2a 2
1 2a 10
32 4bc 1
32 4bc 4
32 r3s 8
I want the end result to look like this (I do not want the word "subtotal"
anywhere on my worksheet):
Location ProductID Qty
1 2a 17
32 4bc 5
32 r3s 8
and delete the original data rows so the subtotaled rows are the only rows
in the worksheet.
(I am using Excel 2003 and need all the rows I can get)
Any help is greatly appreciated.