• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Make sum of duplicate names in list.

Belleke

Well-Known Member
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.
 

Attachments

  • chandoo list.xlsb
    9.6 KB · Views: 9
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
 
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
 
Belleke
Hmm?
I reread Your case ...
As You wanted: Make sum of duplicate names in list.
How many duplicate names are there?
... two?
... hmm?
 

Attachments

  • chandoo list.xlsb
    18.4 KB · Views: 2
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.
 
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
 

Attachments

  • chandoo list.xlsb
    29.3 KB · Views: 4
@vletm & YasserKhalil
Thanks for your time, problem is solved.
This one I like.:DD
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.
 
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

  • Copy of chandoo list.xlsx
    22.6 KB · Views: 2
@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.
 
Back
Top