Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Please help
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! |
#2
|
|||
|
|||
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) "bao-bao" wrote in message ... 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! |
#3
|
|||
|
|||
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/assistan...lconPT101.aspx bao-bao wrote: 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! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|