Hi,
I want a VBA that searches for duplicates, triplicates, or more and combine the cellvalue that is next to the duplicates. Also, if it combines them, it should only use the last letter. I have a code that works partially, but I'm stuck.
For example, I have this list:
1 |
2014A |
2 |
2014B |
3 |
2014C |
1 |
2014D |
4 |
2014E |
1 |
2014F |
I want it to combine the A, D and F like this:
1 |
2014A |
|
1 |
A/D/F |
2 |
2014B |
|
2 |
2014B |
3 |
2014C |
|
3 |
2014C |
1 |
2014D |
|
4 |
2014E |
4 |
2014E |
|
|
|
1 |
2014F |
|
|
|
I created the following script, but i can't get it to use the last letter. Also it creates a " / / " when the cells are empty. Can somebody help me? I'm stuck.
Formula:
Sub test()
Dim toAdd As Boolean, uniqueNumbers As Integer, columnB As Integer, columnE 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 columnB = 2 To 10
For columnE = 1 To uniqueNumbers
If Cells(columnB, 1).Value = Cells(columnE, 4).Value Then
toAdd = False
A = Cells(columnB, 2).Value
B = Cells(columnE, 5).Value
Cells(columnE, 5).Value = B & "/" & A
End If
Next columnE
If toAdd = True Then
Cells(uniqueNumbers + 1, 4).Value = Cells(columnB, 1).Value
Cells(uniqueNumbers + 1, 5).Value = Cells(columnB, 2).Value
uniqueNumbers = uniqueNumbers + 1
End If
toAdd = True
Next columnB
End Sub
Bookmarks