Please help

B

bao-bao

Hi, I have a talbe including two or more same name, I
want to make it from:

tom 10
Mike 10
tom 20
jerry 30

to:
tom 30
Mike 10
jerry 30

Please help, Thank you!
 
B

Bob Phillips

Sub TidyData()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim iRow
Dim sFormula As String

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
j = 0
For i = 1 To iLastRow
On Error Resume Next
iRow = Application.Match(Cells(i, "A").Value, Columns(3), 0)
If IsError(iRow) Then
j = j + 1
Cells(j, "C").Value = Cells(i, "A").Value
sFormula = "SUMPRODUCT(--(A1:A" & iLastRow & "=""" & Cells(i,
"A").Value & """)," & "B1:B" & iLastRow & ")"
Cells(j, "D").Value = Evaluate(sFormula)
End If
Next i

Columns("A:B").Delete

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Some more options...
If you add a header row and sort your data, you can use:
data|subtotals
Then you can hide the details using the outlining symbols at the left.

Or you could use a pivottable
Add headers
Select your range
Data|Pivottable...
follow the wizard until you get to a step that has a "Layout" button on it.
Hit that Layout button.

Drag the header for column A to the Row field
drag the header for column B to the Data field
If you don't see "SUM OF" in that data field, then double click on it and make
choose "Sum of"

Finish up the wizard.

If you want to read more about the pivottable stuff, you may want to look at
some 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
 
Top