Complicated Duplicate Issue

file now attached

thanks

Not really what I wanted, but thanks, it is much more complicated than an advanced filter

The criteria is as follows

ID1 is linked to one+ ID2's, no single ID2 can be linked to the same ID1 more than once, but an ID2 can be linked to two different ID1's, for example

ID1 ID2 RESULT
1 A DUPLICATE
1 A DUPLICATE
1 A DUPLICATE
2 B NOT A DUPLICATE
2 C NOT A DUPLICATE
3 C NOT A DUPLICATE
4 D DUPLICATE
4 D DUPLICATE

Is there there away to automate this as I have a huge data set to apply this to so manual checks is out of the question

If anyone can help please help

Hi Portuga

Thanks for the code, it works spot on, I didnt quite get it right from you reply but it I managed to get it right. Thanks again, great help.

Keith

Originally Posted by Portuga

Hi,

The obvious choice would be the advanced filter. Choose both columns and then filter by unique entries.
Or

the code bellow will flag duplicate entries in red:
(www.techonthenet.com)

Sub TestForDups()

    Dim LLoop As Integer
    Dim LTestLoop As Integer
    Dim LClearRange As String

    Dim Lrows As Integer
    Dim LRange As String

    'Column A values
    Dim LChangedValue As String
    Dim LTestValue As String

    'Column B values
    Dim LChangedValueB As String
    Dim LTestValueB As String

    'Test first 200 rows in spreadsheet for uniqueness
    Lrows = 200
    LLoop = 2

    'Clear all flags
    LClearRange = "A2:B" & Lrows
    Range(LClearRange).Interior.ColorIndex = xlNone

    'Check first 200 rows in spreadsheet
    While LLoop <= Lrows
        LChangedValue = "A" & CStr(LLoop)
        LChangedValueB = "B" & CStr(LLoop)

        If Len(Range(LChangedValue).Value) > 0 Then

            'Test each value for uniqueness
            LTestLoop = 2
            While LTestLoop <= Lrows
                If LLoop <> LTestLoop Then
                    LTestValue = "A" & CStr(LTestLoop)
                    LTestValueB = "B" & CStr(LTestLoop)
                    'Value has been duplicated in another cell
                    If (Range(LChangedValue).Value = Range(LTestValue).Value) And (Range(LChangedValueB).Value = Range(LTestValueB).Value) Then
                        'Set the background color to red in column A
                        Range(LChangedValue).Interior.ColorIndex = 3
                        Range(LTestValue).Interior.ColorIndex = 3

                        'Set the background color to red in column B
                        Range(LChangedValueB).Interior.ColorIndex = 3
                        Range(LTestValueB).Interior.ColorIndex = 3

                    End If

                End If

                LTestLoop = LTestLoop + 1
            Wend

        End If

        LLoop = LLoop + 1
    Wend

End Sub

This code works spot on, however I need to run this code over 30000 rows, it takes hours to calculate. Is there a way to split the code so it only highlights the 1st 5000 cells that are duplicates but still looking at the whole range?

Thanks

Originally Posted by roasty_1

file now attached

thanks

=IF(AND(COUNTIF($A$2:$A$27,A2)>1,COUNTIF($B$2:$B$27,B2)>1),"dup","")