Complicated spreadsheet
 

I have a large spreasheet that reads as follows (In three col) The list is
sorted on Col A
No Name Amount
1 John 29
2 Jean 25
2 Jean 30
3 George 50
4 Jerry 12
4 Jerry 23

I would like then end product to look like this
No Name Amount
1 John 29
2 Jean 55 (25+30)
3 George 50
4 Jerry 35(12 +23)
I would appreciate any help I can get

Complicated spreadsheet
 

pcor

DataSubtotals based on Name column will give you total for each name.

Collapse all but the subtotals.


Gord Dibben MS Excel MVP


On Mon, 11 Dec 2006 20:14:00 -0800, pcor wrote:

I have a large spreasheet that reads as follows (In three col) The list is
sorted on Col A
No Name Amount
1 John 29
2 Jean 25
2 Jean 30
3 George 50
4 Jerry 12
4 Jerry 23

I would like then end product to look like this
No Name Amount
1 John 29
2 Jean 55 (25+30)
3 George 50
4 Jerry 35(12 +23)
I would appreciate any help I can get

Complicated spreadsheet
 

In column D,
=SUMPRODUCT(--($B$1:$B$5000=B1),--($C$1:$C$5000))
Adjust the ranges to suit and drag down to the end of the list,
Regards,
Alan.
"pcor" wrote in message
...
I have a large spreasheet that reads as follows (In three col) The list is
sorted on Col A
No Name Amount
1 John 29
2 Jean 25
2 Jean 30
3 George 50
4 Jerry 12
4 Jerry 23

I would like then end product to look like this
No Name Amount
1 John 29
2 Jean 55 (25+30)
3 George 50
4 Jerry 35(12 +23)
I would appreciate any help I can get

Complicated spreadsheet
 

Try this

Sub m()
Dim lastrow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
Cells(i - 1, 3) = Cells(i - 1, 3) + Cells(i, 3)
Rows(i).Delete
End If
Next i
End Sub

Cheers

"pcor" wrote in message
...
I have a large spreasheet that reads as follows (In three col) The list is
sorted on Col A
No Name Amount
1 John 29
2 Jean 25
2 Jean 30
3 George 50
4 Jerry 12
4 Jerry 23

I would like then end product to look like this
No Name Amount
1 John 29
2 Jean 55 (25+30)
3 George 50
4 Jerry 35(12 +23)
I would appreciate any help I can get

Complicated spreadsheet
 

Thanks you very much to Gord,Alan and PY. This has helped me a great deal
Tahnsk again'


"pcor" wrote:

I have a large spreasheet that reads as follows (In three col) The list is
sorted on Col A
No Name Amount
1 John 29
2 Jean 25
2 Jean 30
3 George 50
4 Jerry 12
4 Jerry 23

I would like then end product to look like this
No Name Amount
1 John 29
2 Jean 55 (25+30)
3 George 50
4 Jerry 35(12 +23)
I would appreciate any help I can get