Make sum of duplicate names in list.

[Belleke]

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

Thanks in advance.

 

[vletm]

Belleke
Something like You wrote...

 

[YasserKhalil]

Try this

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]

Hello,vletm and YasserKhalil,
Thanks for the fast reply.
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]

You're welcome. Glad I can offer some help

 

[vletm]

Belleke
Hmm?
I reread Your case ...
As You wanted: Make sum of duplicate names in list.
How many duplicate names are there?
... two?
... hmm?

 

[Belleke]

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]

Belleke
Okay ... Ok, more explanation.
If Your original is like:
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

 

[Belleke]

@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]

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).

 

[Belleke]

@Guido,
Thanks for the reply.
I'll test your idea.
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]

Also works with a dynamic range name with offset function.