Macro/Formula Summing Values Meeting 2 Criteria

[Mr_Nivek]

Hello,

I was wondering if there was a way to create a macro or formula that could help me organize some invoice data. I would like it to search column A (Name) and column B (ID) and find duplicates within that range. For any A/B adjacent cell pairs with the same exact values, I'd like to have their corresponding C column values summed.

For example:

In the table below, I'd like rows containing the BOTH the name "Walmart" and ID "1" to have their fee values summed in a separate column. Also, if it's possible, I'd like the corresponding name and ID to be listed next to the calculated sum.

If this is possible and anyone has any guidance, I would be enormously grateful. Thank you for your time.

Name	ID	Fee
Walmart	1	10
Walmart	2	15
Walmart	1	10
Target	3	0
Target	3	15
Target	4	5
Target	3	15
Kmart	5	20

<TBODY>
</TBODY>

 

[MickG]

Try this :- Results columns "D & E"

[COLOR="Navy"]Sub[/COLOR] MG01Aug11
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
n = 1
Range("D1:E1") = Array("Name/ID", "Tot_Fee")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Twn = Dn & "/" & Dn(, 2)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Twn, n
            Cells(n, "D") = Twn: Cells(n, "E") = Dn.Offset(, 2)
        [COLOR="Navy"]Else[/COLOR]
            Cells(.Item(Twn), "E") = Cells(.Item(Twn), "E") + Dn.Offset(, 2)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mr_Nivek]

Hello,

Thank you so much for taking a moment to help me.

I'm sorry, but I tried tweaking your suggestion to fit the format I'm using, but to no avail.

Would you be able to suggest a solution that uses the name, id, and fee information starting in row 14 as shown below and outputs the data in columns K and L, starting in row 14.

Thanks again for the assistance!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Aug04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
n = 13
Range("K13:L13") = Array("Name/ID", "Tot_Fee")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A14"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Twn = Dn & "/" & Dn(, 2)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Twn, n
            Cells(n, "K") = Twn: Cells(n, "L") = Dn.Offset(, 8)
        [COLOR="Navy"]Else[/COLOR]
            Cells(.Item(Twn), "L") = Cells(.Item(Twn), "L") + Dn.Offset(, 8)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick