Make sum of duplicate names in list.

Belleke

Active Member
Hi,
I would like to sum double values.
I made an example with what I have and what I would like to achieve.

Attachments

• 9.6 KB Views: 8

vletm

Excel Ninja
Belleke
Something like You wrote...

Attachments

• 9.2 KB Views: 5

YasserKhalil

Well-Known Member
Try this
Code:
``````Sub Test()
Dim a, i As Long, ii As Long, txt As String

a = Cells(1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
txt = a(i, 1)
If Not .Exists(txt) Then
.Item(txt) = .Count + 1
For ii = 1 To UBound(a, 2)
a(.Count, ii) = a(i, ii)
Next ii
Else
a(.Item(txt), 3) = a(.Item(txt), 3) + a(i, 3)
End If
Next i
i = .Count
End With

[H2].Resize(i, UBound(a, 2)) = a
End Sub``````

Belleke

Active Member
Hello,vletm and YasserKhalil,
My problem is solved.
I choose the vba solution because it is for the local footballclub with Excel noobs behind the bar, the formulas won't last long.
Thank you and have a nice weekend

YasserKhalil

Well-Known Member
You're welcome. Glad I can offer some help

vletm

Excel Ninja
Belleke
Hmm?
As You wanted: Make sum of duplicate names in list.
How many duplicate names are there?
... two?
... hmm?

Attachments

• 18.4 KB Views: 2

Belleke

Active Member
Ok, more explanation.
There are +- 120 members, and they all can be multiple times in the list.
I don't want a separeted list of the duplicates.
Ideal solution would be the existing list replaced by a list were the duplicates are summed up and sorted by the ID number.
The code for summing is what Yasser provided that I want, except that it not replaces the existing column but makes a second list. starting in Column H., and it is not sorted from 1 to ...
Thanks for looking at the problem.

vletm

Excel Ninja
Belleke
Okay ... Ok, more explanation.

Make sum of duplicate names in list.
I would like to sum double values.

... and now
... You write
I don't want a separeted list of the duplicates. ... sorting
You would get that in the 1st time, if ...

> Check this .. not to H-column but somewhere near

Attachments

• 29.3 KB Views: 4

Belleke

Active Member
@vletm & YasserKhalil
Thanks for your time, problem is solved.
This one I like.
not to H-column but somewhere near
My knowledge of VBA is not that bad that I can't change the code of YasserKhalil from column H to E.
Thanks again Vletm & YasserKhalil
Have a nice weekend.

GraH - Guido

Well-Known Member
Belleke, 2 simple alternatives without code, without formulae.

Pivot table and Power Query Group By. If you would store data in a table, using a simple refresh does the job in both cases when new data is added (note with a sheet event trigger, this can be automated, but that you know better then me).

Attachments

• 22.6 KB Views: 2

Belleke

Active Member
@Guido,
The data in these 3 columns comes from another sheet with 12 colums and the result is not a table. (list of members that not payed there bill)
I'll have to see what I can do about that.
Have a nice Sunday.

GraH - Guido

Well-Known Member
Also works with a dynamic range name with offset function.

Attachments

• 21.1 KB Views: 2