[ finding duplicates and combining part of cell value next to duplicate ]

Let's start with an example of how some values in my workbook look like:

       A        B         C
1     14-001   2014G001
2     14-002   2014G002
3     14-001   2014I002

I want to make a script that copies the values in column B to C, and combines the last 4 characters of these values if the cell value in column A are the same. after running the script it should look like:

       A        B         C
1     14-001   2014G001   G001/I001
2     14-002   2014G002   G002
3     14-001   2014I001

I never had to look for duplicates and I can't find a similar problem on the net. Can somebody help me out?

Thanks!!

Answer 1

I actually worked it out myself :) Now I have to find out how to change it when there are 3 of the same..Somebody?

Sub test()

Dim toAdd As Boolean, uniqueNumbers As Integer, i As Integer, j As Integer

Dim A$, B$

Cells(1, 4).Value = Cells(1, 1).Value
Cells(1, 5).Value = Cells(1, 2).Value

uniqueNumbers = 1
toAdd = True

For i = 2 To 30
For j = 1 To uniqueNumbers
If Cells(i, 1).Value = Cells(j, 4).Value Then
    toAdd = False

    A = Right(Cells(i, 2), 4)
    B = Right(Cells(j, 2), 4)
    Cells(j, 5).Value = A & " / " & B


End If
Next j

If toAdd = True Then
Cells(uniqueNumbers + 1, 4).Value = Cells(i, 1).Value
Cells(uniqueNumbers + 1, 5).Value = Cells(i, 2).Value
uniqueNumbers = uniqueNumbers + 1
End If

toAdd = True

Next i

End Sub