1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Belleke, Feb 9, 2019.

  1. Belleke

    Belleke Active Member

    Messages:
    549
    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.

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Belleke
    Something like You wrote...

    Attached Files:

    Belleke likes this.
  3. YasserKhalil

    YasserKhalil Well-Known Member

    Messages:
    1,016
    Try this
    Code (vb):
    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
    Thomas Kuriakose and Belleke like this.
  4. Belleke

    Belleke Active Member

    Messages:
    549
    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
  5. YasserKhalil

    YasserKhalil Well-Known Member

    Messages:
    1,016
    You're welcome. Glad I can offer some help
  6. vletm

    vletm Excel Ninja

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

    Attached Files:

  7. Belleke

    Belleke Active Member

    Messages:
    549
    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.
  8. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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

    Attached Files:

    sathishsusa and Belleke like this.
  9. Belleke

    Belleke Active Member

    Messages:
    549
    @vletm & YasserKhalil
    Thanks for your time, problem is solved.
    This one I like.:DD
    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.
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    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).

    Attached Files:

    Thomas Kuriakose and Belleke like this.
  11. Belleke

    Belleke Active Member

    Messages:
    549
    @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.
  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    945
    Also works with a dynamic range name with offset function.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page