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.
 

Some videos you may like

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.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,452
Messages
5,572,209
Members
412,448
Latest member
ManuW
Top