Sum of common rows

[Holly Bodger]

I am trying to find an automated way of summing common row
data. For example:

ORIGINAL DATA
Row1 123 John Smith
Row2 456 John Smith
Row3 678 John Smith
Row4 324 Dave Jones
Row5 678 Dave Jones

DESIRED DATA
Row1 1257 John Smith
Row2 1002 Dave Jones

I have several thousand rows and don't want to write
something that is specific to the data that appears in the
second column.

Any ideas?

 

[Ron de Bruin]

Try this

Row 1(col A and B) must have a header like Number and Name
It will make a Unique list in Col C and the sum in Col D

Sub test()
Dim LastRow As Long
With Sheets("sheet1")
.Range("b:b").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("c1"), _
CriteriaRange:="", Unique:=True

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Each cell In .Range("c2:c" & LastRow)
If cell.Value = "" Then Exit Sub
cell.Offset(0, 1) = _
Application.WorksheetFunction.SumIf( _
.Range("b:b"), "=" & cell.Value, .Range("A:A"))
Next
End With
End Sub

 

[Ron de Bruin]

Dave posted this links today

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

 

[Ken Wright]

The other option of course, is to simply sort on that second column, select all your data and do
Data / Subtotals and then choose which fields you want to sum/count etc

 

[Ken Wright]

Thanks Ron - appreciate it.

 

[Marty]

A pivot table is the answer to your question. Click Data

PivotTable PivotChart > Next > Select the data range

(this is usually done automatically) select Next. From
here it depends on which version of Excel you are using
but you will need to click Layout or a dialog box is
already open that says Layout. Click and drag the "Name
Field" to where it says ROW. Then click and drag the Count
(let's call it Count) field you want to SUM into the area
where it says DATA. If it does not say "Sum of Count"
double click that button looking item in the Data area of
the dialog and you will see all kinds of ways you can
display the data. Select "SUM" Click OK then Click
FINISHED. And there you are!

Marty

 

[Holly Bodger]

That's works brilliantly! Thanks a million...

Holly