Combine cell values based on Duplicates

  • Hi All,

    need some help. I am trying to find duplicate values in 1st Column and combine values in 2nd column and want to sum values in 3rd column.


    For an example.

    1st Col 2nd Col 3rd Col
    5789 Z 1
    5789 U 1
    5789 X 1
    5789 Y 1
    7654 U 1
    7698 U 1
    7698 A 1
    7698 O 1
    7698 P 1
    3459 U 1
    9762 U 1
    9762 X 1
    9762 F 1
    9762 I 1
    9762 L 1



    Would become :


    1st Col 2nd Col 3rd Col
    5789 Z,U,X,Y 4
    5789 Z,U,X,Y 4
    5789 Z,U,X,Y 4
    5789 Z,U,X,Y 4
    7654 U 1
    7698 U,A,O,P 4
    7698 U,A,O,P 4
    7698 U,A,O,P 4
    7698 U,A,O,P 4
    3459 U 1
    9762 U,X,F,I,L 5
    9762 U,X,F,I,L 5
    9762 U,X,F,I,L 5
    9762 U,X,F,I,L 5
    9762 U,X,F,I,L 5



    I am using below code, please help.


    Sub Task111()

    Dim lngRow As Long

    For lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1

    If StrComp(Range("A" & lngRow), Range("A" & lngRow - 1), vbTextCompare) = 0 Then

    If Range("A" & lngRow) <> "" Then

    Range("B" & lngRow - 1) = Range("B" & lngRow - 1) & ";" & Range("B" & lngRow)

    Range("C" & lngRow - 1) = Range("C" & lngRow - 1) + Range("C" & lngRow)

    End If

    End If

    Next

    End Sub

  • Are you using Excel 2016 or later? If so, you could use formulas.


    With your original data in A2:C16, E2 would be =A2, F2 would be =TEXTJOIN(",",1,IF($A$2:$A$16=E2,$B$2:$B$16,"")) [and array formula], and G2 would be =SUMIFS($C$2:$C$16,$A$2:$A$16,E2). Fill E2:G2 down into E3:G16 to produce the 2nd table above.


    If you believe you have to do this with VBA, and without using TEXTJOIN, but assuming the source range is sorted on the 1st column,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!