Sum the amounts of lines with the same criteria

[maxon]

Hello Guys,

I need your support how to build the code properly to fix below problem.

I need to sum the amounts of lines, where "Name" and "ID number" are the same. Below you find an extract of table, normally its much bigger about 500-600 lines.

Name	Code	ID number	Amount	Ccy	Date	Sum
ClientA	MD554	AABBCCDD	4,052.80	USD	09/01/2017
ClientA	MD557	AABBCCDD	6,543.50	USD	09/01/2017
ClientA	MD558	AABBCCDD	8,198.08	USD	09/01/2017
ClientA	MD567	AABBCCDD	8,967.30	USD	09/01/2017
ClientA	MD568	AABBCCDD	5,011.87	USD	09/01/2017	32,773.55
ClientA	MD567	ABCD	6,475.00	USD	09/01/2017
ClientA	MD569	ABCD	11,100.00	USD	09/01/2017	17,575.00
ClientA	MD567	AABBCC	1,554.14	USD	09/01/2017
ClientA	MD568	AABBCC	806.22	USD	09/01/2017	2,360.36
ClientA	MD567	ABCDEF	340.18	USD	09/01/2017
ClientA	MD568	ABCDEF	227.15	USD	09/01/2017	567.33
ClientA	MD567	ABBCCDDEE	3,146.53	USD	09/01/2017
ClientA	MD567	ABBCCDDEE	3,146.53	USD	09/01/2017	6,293.06
ClientA	MD567	ABC1	6,375.00	USD	09/01/2017
ClientA	MD567	ABC4	7,312.50	USD	09/01/2017

<tbody>
</tbody>

This column Sum has been done by me.

I would be grateful if you could tell me how should look like proper code.

Thanks!
max

 

[MickG]

Try this for results in column "G".

[COLOR="Navy"]Sub[/COLOR] MG04Sep29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Q [COLOR="Navy"]As[/COLOR] Variant
[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
Txt = Dn.Value & "," & Dn.Offset(, 2).Value
[COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
      .Add Txt, Array(Dn.Offset(, 3).Value, Dn)
[COLOR="Navy"]Else[/COLOR]
    Q = .Item(Txt)
        Q(0) = Q(0) + Dn.Offset(, 3).Value
        [COLOR="Navy"]Set[/COLOR] Q(1) = Dn
     .Item(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    .Item(K)(1).Offset(, 6).Value = Format(.Item(K)(0), "#,##0.00")
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[etaf]

i would use a pivot table to get all clients and ids and then the same

 

[FormR]

Hi, you could also try a formula if your data is sorted by the "name" and "ID number":

Excel 2013/2016
	A	B	C	D	E	F	G
1	Name	Code	ID number	Amount	Ccy	Date	Sum
2	ClientA	MD554	AABBCCDD	4,052.80	USD	09/01/2017
3	ClientA	MD557	AABBCCDD	6,543.50	USD	09/01/2017
4	ClientA	MD558	AABBCCDD	8,198.08	USD	09/01/2017
5	ClientA	MD567	AABBCCDD	8,967.30	USD	09/01/2017
6	ClientA	MD568	AABBCCDD	5,011.87	USD	09/01/2017	32773.55
7	ClientA	MD567	ABCD	6,475.00	USD	09/01/2017
8	ClientA	MD569	ABCD	11,100.00	USD	09/01/2017	17575
9	ClientA	MD567	AABBCC	1,554.14	USD	09/01/2017
10	ClientA	MD568	AABBCC	806.22	USD	09/01/2017	2360.36
11	ClientA	MD567	ABCDEF	340.18	USD	09/01/2017
12	ClientA	MD568	ABCDEF	227.15	USD	09/01/2017	567.33
13	ClientA	MD567	ABBCCDDEE	3,146.53	USD	09/01/2017
14	ClientA	MD567	ABBCCDDEE	3,146.53	USD	09/01/2017	6293.06
15	ClientA	MD567	ABC1	6,375.00	USD	09/01/2017	6375
16	ClientA	MD567	ABC4	7,312.50	USD	09/01/2017	7312.5
Sheet1

Cell Formulas
Range	Formula
G2	=IF(AND(A2=A3,C2=C3),"",SUMIFS(D$2:D2,A$2:A2,A2,C$2:C2,C2))

This could also be applied by code if needed:

With Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(AND(A2=A3,C2=C3),"""",SUMIFS(D$2:D2,A$2:A2,A2,C$2:C2,C2))"
    .Value = .Value
End With

 

[maxon]

First of all thanks for quick reply!

I have already tested and code of FormR is working perfectly.
The code of MickG is not present sum of amounts in the column, but maybe I made a mistake becouse I modified one column.

MickG could you please explain to me your code - I would be really grateful.

 

[maxon]

I figure it out, is it possible to add something to your code MickG that I will not inserting value if there is nothing to sum?

 

[MickG]

Can you show an example of the data where the code fails, showing both the correct result and the incorrect result.
At the moment I get the code to fail !!!

 

[maxon]

Sorry MickG, its working perfectly! I made a mistake when I was changing the column, becouse I have added one. Thanks a lot! you are amazing

 

[MickG]

You're welcome