[ 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