# 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

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,

• Hi Fzz, I am using MS Excel 2013, however thank you very much for the VBA code, it is working well really appreciate your help

## Participate now!

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