Remove Duplicates By Id's

jimmykii · Feb 19th 2007

Would like have some feedback on how to remove the duplicate by ID..
Ex. ( 3 Column only)
A. ID
B. Name
C. Text ( this column need to be removed all the duplicates by ID )

Sample data:

19 John NH MK CL CN
19 Kerry JK MK CN AL
16 Dii JK MK CN AL
15 John SS

Desired Result:

19 John NH CL
19 Kerry JK AL
16 Dii JK MK CN AL
15 John SS

condition details - If the two record’s have a same ID’s ( our ex. Like ID 19) then would like to compare column three ( Text column – single string) , and the column can have a multiple code’s or single code ex. MK or Multiple code like a NH MK CL CN. And each code consist two character only, but how many code’s each ID holds varies.

And this what I want compare, like our sample example

Row1: 19 John NH MK CL CN
Row2: 19 Kerry JK MK CN AL

This case both rows have 4 code’s ( row 1/ column 3 : NH MK CL CN and row 2/ column3: JK MK CN AL) in this case, I would like to remove only codes that show up on both record’s, which is : MK and CN, then our new output looks like :

19 John NH CL
19 Kerry JK AL

But if the two record’s ( text clumn) don’t have any common codes, then the output will remain the same Ex.

19 John NH KK CL LL
19 Kerry JK MK CN AL

This case the output remains same:

19 John NH KK CL LL
19 Kerry JK MK CN AL

and finally if the two row’s don’t have same ID, then don’t compare.

This are like 1000’s record it’s very difficult to compare manually..

Thanks.
Jimmy

**Kenneth Hobson** · Feb 19th 2007

Re: Remove Duplicates By Id's

1. Is the data sorted by ID initially?
2. Will column 3 data always be two characters, space, two characters, etc.?
3. Would you want the results in a new column or overwrite current data?

jimmykii · Feb 19th 2007

Re: Remove Duplicates By Id's

Thanks.
1. Is the data sorted by ID initially? Yes..

2. Will column 3 data always be two characters, space, two characters, etc.?
Yes..
3. Would you want the results in a new column or overwrite current data?
Prefered new column.

Miffo · Feb 19th 2007

Re: Remove Duplicates By Id's

Hello there!

Before I begin I just want to say that my code probably isnt the best one, but since I felt I needed some practise I sat down and wrote some code for this.
I did the assumption that one ID can occur more than just twice, so you have more then just two rows to compare (that would have been SOOOO much easier...). I tried my code a few times, and it worked for me on your example + 2 added lines with the ID 19 (as in your example) and codes that are similar to the ones in your example.

Heres the code:

Code

Option Explicit


Sub Compare()


Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim StartRow As Integer
Dim Duplicates As Integer
Dim CurrentCode As String
Dim CurrentCodeStr As String
Dim CodeLn As Integer
Dim PlayCol As Integer
Dim DupCodeFound As Boolean


i = 1
Do While Not Cells(i, 1).Value = Empty
  If Cells(i, 1).Value = Cells(i + 1, 1).Value Then 'Checking for duplicate ID´s
    StartRow = i
    Do While Cells(i, 1).Value = Cells(StartRow, 1).Value 'Collecting all duplicate ID´s
      i = i + 1
    Loop
    i = i - 1
    For Duplicates = StartRow To i 'For every duplicate ID we found
      CodeLn = Len(Cells(Duplicates, 3).Value) 'We find out how long the Code-string is
      CodeLn = (CodeLn + 1) / 3 'and thereby we know how many codes it is
      CurrentCodeStr = Cells(Duplicates, 3).Value 'The string for the current code that will be truncated depending on if we find other strings with the same code
      For x = 0 To CodeLn - 1 'For every code in the codestring
        CurrentCode = Mid(Cells(Duplicates, 3).Value, 1 + (x * 3), 2) 'The code we´re going to check for in the other codestrings
        DupCodeFound = False
        For y = StartRow + 1 To i
          If Not y = Duplicates Then 'We dont want to check the codestring againt itself
            If Cells(y, 4).Value = Empty Then 'We want to look at the right column. If we have written anything to Column 4 already, thats the one we´re using for the next check
              PlayCol = 3
            Else
              PlayCol = 4
            End If
            If InStr(1, Cells(y, PlayCol).Value, CurrentCode) > 0 Then 'If we find the code in another codestring
              Cells(y, 4).Value = Mid(Cells(y, PlayCol).Value, 1, (InStr(1, Cells(y, PlayCol).Value, CurrentCode) - 1)) & Mid(Cells(y, PlayCol).Value, (InStr(1, Cells(y, PlayCol).Value, CurrentCode) + 3), Len(Cells(y, PlayCol).Value)) 'We rebuild the string without the found code, and place it in the next column, so we dont overwrite the original
              DupCodeFound = True 'A flag so we know we have removed this code from another codestring
            End If
          End If
        Next y
        'And finally removing the code from the current one and outputting it in Column 4
        If DupCodeFound Then 'ONLY if we already have removed anything from another codestring
          Cells(Duplicates, 4).Value = Mid(CurrentCodeStr, 1, (InStr(1, CurrentCodeStr, CurrentCode) - 1)) & Mid(CurrentCodeStr, (InStr(1, CurrentCodeStr, CurrentCode) + 3), Len(CurrentCodeStr))
          CurrentCodeStr = Cells(Duplicates, 4).Value 'We change the current codestring thats gonna be output next time we come here
        End If
      Next x
    Next Duplicates
  End If
  i = i + 1
Loop


End Sub

Display More

As I said, there are most likely better ways of doing this, so you might want to wait until someones else helps you too.

I hope I have commented the code enough to understand the logic in it (its not easy all the time)...

Hope it works for you...

Kind regards
/Henrik

jimmykii · Feb 20th 2007

Re: Remove Duplicates By Id's

Thanks Henrik..Works great!

Jimmy

Remove Duplicates By Id's

Participate now!

Share