Need macro to total up the same name

joram7

Board Regular
Joined
May 12, 2005
Messages
115
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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
 
Upvote 0
thanks for the reply,

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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
How about:

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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top