How do I identify and delete rows with matching data mixed between multiple columns?

[ChrisJP]

Example:

	Column 1	Column 2
Row 1	ABC123	MNO456
Row 2	ZZZ987	YYY789
Row 3	MNO456	ABC123

<tbody>
</tbody>

How could I identify Row 1 and Row 3 as being matches/duplicates rows, when the columns may have the data swapped?

Ideally I would want the a VB script or way to delete the Row 3 automatically since it is a duplicate.

This may be basic, but I've been searching for a while now and can't quite find the answer. I appreciate any help you can provide.

 

[JLGWhiz]

Sub t()
Dim lr As Long, i As Long, fn As Range
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        For i = lr To 2 Step -1
            Set fn = .Range("B:B").Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If fn.Offset(, -1).Value = .Cells(i, 2).Value Then
                        .Rows(i).Delete
                    End If
                End If
        Next
    End With
End Sub

 

[ChrisJP]

JLGWhiz,

This seems to work for a portion of my data, but it seems to fail at a certain point. On my data below, it fails to recognize G006134/G004402 as a duplicate. Any ideas why this might happen? I appreciate the help!

C1	C2
B34558	16612
B41627	68418
G000659	86130
G001594	77539
G002224	96232
G002544	G004514
G004514	G002544
G002771	G002772
G002771	L49435
G002772	G002771
G002772	L49435
G002814	G004402
G002814	G006134
G002814	L51316
G004402	G006134
G004402	G002814
G004402	L51316
G004402	G006031
G006031	G004402
G006031	G006134
G006134	G004402
G006134	G002814
G006134	L51316
G006134	G006031
L51316	G002814
L51316	G004402
L51316	G006134
G005639	L55069
G005639	L58082
G006683	L58082
L55069	G005639
L55069	L58082
L58082	G005639
L58082	G006683
L58082	L55069

<colgroup><col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> </colgroup><tbody>
</tbody>

 

[JLGWhiz]

Check for Ohs entered for Zeros. O v. 0

 

[ChrisJP]

Did a search for O vs Zero, no letter O.

 

[JLGWhiz]

Did a search for O vs Zero, no letter O.

I would think it has to be a binary difference of some type, if all the others worked but that one set of characters. Try retyping them and see if they still won't match. I am assuming that you added the bold font and underline for illustration purposes. If not, that is your problem. The underline will not match, but the bold font should.

 

[JLGWhiz]

I was wrong, it should recognize the match, even with the underline.

Try this mod

Sub t()
Dim lr As Long, i As Long, fn As Range
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        For i = lr To 2 Step -1
            Set fn = .Range("B:B").Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If [COLOR=#FF0000]Trim[/COLOR](fn.Offset(, -1).Value) = [COLOR=#FF0000]Trim[/COLOR](.Cells(i, 2).Value) Then
                        .Rows(i).Delete
                    End If
                End If
        Next
    End With
End Sub

 

[ChrisJP]

The mod ended with the same result, still missed some.

The underline was just to highlight one of the examples. There were more missed, the just the one.

Also missed:
L55069/L58082 and L58082/L55069
L51316/G004402 and G004402/L51316

 

[ChrisJP]

I retyped my sample data to see if it was formatting and the problem still occurred.

 

[JLGWhiz]

Well, it has me stymied. I thought the Trim function would fix it if it was not a binary problem. If I think of anything else I will post back.

One other thing. Do you have duplicate entries in column A? Never mind, I see that you do. I think I can fix the problem.