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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

joram7

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

ADVERTISEMENT

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?
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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
Joined
May 12, 2005
Messages
115
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
Joined
May 12, 2005
Messages
115
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
Joined
May 28, 2004
Messages
4,237
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,001
Messages
5,834,820
Members
430,324
Latest member
bosphoruskid

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
Top