# Need macro to total up the same name

#### joram7

##### Board Regular
Hi can anyone help with this,

I have this data where i need to sum;

abcd 216.00
abcd 572.00
eeee 649.00
zzz 396.00
eeee 564.00
eeee 1,194.00
fffff 731.00
fffff 54.24
zzz 731.00
zzz 885.00
abcd 598.24
zzz 241.24
eeee 153.00
zzz 403.00

I need macro to sum allthe same group into

eeee (total here)
zzz
fffff

the reason i need this is to enable me to do this to thousands of data and used copy and paste to produce a reports.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Tazguy37

##### MrExcel MVP
Have you tried using the Data, Subtotals feature?

#### joram7

##### Board Regular
Yup, but by using subtotal , i wont be able to copy the data and paste as a reports, i will still need to clear things up coz i'm dealing with few thousands data here. After that i need to sort up and make a top ten reports

#### Tazguy37

##### MrExcel MVP
Then try SUMIF:

=SUMIF(A1:A10,"abcd",B1:B10)

If the labels are in Column A and the data in Column B. Adjust the ranges to suit. HTH

#### joram7

##### Board Regular

This won't really work because i have like 700 products and it goes random, is there any vb macro which to make it match. then sum?

#### joram7

##### Board Regular
any help? #### Tazguy37

##### MrExcel MVP

Try this:

Code:
``````Sub getTotals()
'Assumes data is in A:B (no header row)

Range("A1:B1").Insert -4121
Range("A1").Value = "Category"
Range("B1").Value = "Value"
Range("A1:B" & Range("B:B").Find("*", , , , , 2).Row).Copy Range("D1")
With Range("D1:E" & Range("B:B").Find("*", , , , , 2).Row)
.Sort Range("D2"), 1, , , , , , 1, , 0
.Subtotal 1, -4157, Array(2)
.Copy
.PasteSpecial -4163
.AutoFilter 1, "=*Total*", 1, "<>Grand Total"
End With
Range("D:E").SpecialCells(2).Copy Range("G1")
ActiveSheet.AutoFilterMode = 0
Range("D:E").RemoveSubtotal
Range("A1:I1").Delete -4162
Range("D:E").Delete
Columns("E:F").Columns.AutoFit

End Sub``````

#### joram7

##### Board Regular
Excellent, works well. from here i think i should be able to macro remove the word total and auto paste to others sheet. thanks alot tazguy

#### joram7

##### Board Regular
One problem here, is it possible to count the total number next to the total
volume?

example after sorting

aaaa_____8293____5

the 5 refers to 5 aaaa which total up to get 8293.

#### Tazguy37

##### MrExcel MVP

Code:
``````Sub getTotals()
'Assumes data is in A:B (no header row)

Range("A1:B1").Insert -4121
Range("A1").Value = "Category"
Range("B1").Value = "Value"
Range("A1:B" & Range("B:B").Find("*", , , , , 2).Row).Copy Range("D1")
With Range("D1:E" & Range("D:D").Find("*", , , , , 2).Row)
.Sort Range("D2"), 1, , , , , , 1, , 0
.Subtotal 1, -4157, Array(2)
End With
With Range("D1:E" & Range("D:D").Find("*", , , , , 2).Row)
.Copy
.PasteSpecial -4163
.AutoFilter 1, "=*Total*", 1, "<>Grand Total"
End With
Range("D:E").SpecialCells(2).Copy Range("G1")
ActiveSheet.AutoFilterMode = 0
Range("D:E").RemoveSubtotal
Range("A1:I1").Delete -4162
Range("D:E").Delete
Range("G1:G" & Range("G:G").Find("*", , , , , 2).Row).FormulaR1C1 = "=COUNTIF(C[-6],LEFT(RC[-2],FIND("" "",RC[-2])-1))"
Columns("E:F").Columns.AutoFit

End Sub``````

Replies
1
Views
268