Show duplicate values for combination and it's reverse version

[mrugesh]

I have 4 digit numbers in two columns. What I need to see is if the combination of them is being repeated below. So, if the number in column A is 1000 & column B is 1300, combination of them will be 10001300. Now I need to see if this combination or it's other reverse version (13001000) is repeating below or not.
Can someone please help me here?

 

[BrianJN1]

You have those two numbers in the same row? And you need to see if that row is repeated in the exact or reverse order?
I want to be clear on your meaning.

 

[mrugesh]

You have those two numbers in the same row? And you need to see if that row is repeated in the exact or reverse order?
I want to be clear on your meaning.

Correct. Those two numbers are in same row and I want to see if the combination is repeated in exact or reverse order.

 

[Peter_SSs]

Would this Conditional Fomatting method suffice?
Select A2:B?? and apply the CF formula shown (adjust the ranges in the formula to match yours)

Excel Workbook
	A	B
1
2	1234	5698
3	1000	1300
4	9000	2568
5	1300	1000
6	5800	3030
7	6598	2222
8	6598	2222
9	1111	6548
10	1000	1300
Repeat Pairs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =SUMPRODUCT(($A$2:$A$10&$B$2:$B$10=$A2&$B2)+($A$2:$A$10&$B$2:$B$10=$B2&$A2))/IF($A2=$B2,2,1)>1	Abc

 

[mrugesh]

Would this Conditional Fomatting method suffice?
Select A2:B?? and apply the CF formula shown (adjust the ranges in the formula to match yours)

Repeat Pairs

	A	B

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 56px;"><col style="width: 56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5698[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]2568[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]5800[/TD]
[TD="align: right"]3030[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]6598[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]2222[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]6598[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]2222[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]6548[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1300[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format A2 1. / Formula is =SUMPRODUCT(($A$2:$A$10&$B$2:$B$10=$A2&$B2)+($A$2:$A$10&$B$2:$B$10=$B2&$A2))/IF($A2=$B2,2,1)>1 <tbody> [TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "]Abc

<tbody>

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank You so much, Peter! This works. You just saved me hours of manual work.

 

[Peter_SSs]

Thank You so much, Peter! This works. You just saved me hours of manual work.

No problem. Glad to help.

 

[sugoimm]

I see you already have this solved but I wanted to try and contribute a VBA solution. Have a good one

Sub code()
Dim ColA As Variant, ColBrow As Variant
Dim rg As Range
Set rg = Range("A1").CurrentRegion
For i = 1 To rg.Rows.Count
    
    ColA = Range("A" & i)
    If Not Range("B1", "B" & rg.Rows.Count).Find(ColA) Is Nothing Then
        ColBrow = Range("B1", "B" & rg.Rows.Count).Find(ColA).Row
        If Range("B" & i) = Range("A" & ColBrow) Then
            Range("A" & i).EntireRow.Interior.Color = RGB(180, 230, 180)
            Range("A" & ColBrow).EntireRow.Interior.Color = RGB(180, 230, 180)
        End If
    End If
    ColB = 0
Next i
End Sub

 

[mrugesh]

I see you already have this solved but I wanted to try and contribute a VBA solution. Have a good one

Sub code()
Dim ColA As Variant, ColBrow As Variant
Dim rg As Range
Set rg = Range("A1").CurrentRegion
For i = 1 To rg.Rows.Count
    
    ColA = Range("A" & i)
    If Not Range("B1", "B" & rg.Rows.Count).Find(ColA) Is Nothing Then
        ColBrow = Range("B1", "B" & rg.Rows.Count).Find(ColA).Row
        If Range("B" & i) = Range("A" & ColBrow) Then
            Range("A" & i).EntireRow.Interior.Color = RGB(180, 230, 180)
            Range("A" & ColBrow).EntireRow.Interior.Color = RGB(180, 230, 180)
        End If
    End If
    ColB = 0
Next i
End Sub

Thanks for this one. I will try this one as well. Always better to have more than one bullet in your gun.

 

[Peter_SSs]

I see you already have this solved but I wanted to try and contribute a VBA solution. Have a good one

Hi sugoimm
I noticed with your code and my sample data, that
- it did not colour rows 7 & 8 even though they are repeats.
- if I added a new row with the values 5656 and 5656 in the 2 columns, it did colour that row even though the row is not duplicated.

 

[Peter_SSs]

I will try this one as well. Always better to have more than one bullet in your gun.

So, if you are interested in a macro alternative, you could try this one which I believe overcomes the issues with the previous code that I mentioned in post 9. I have assumed the actual data starts in row 2.

Sub CheckRepeats()
  Dim d As Object
  Dim a As Variant
  Dim r As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A1", Range("B" & Rows.Count).End(xlUp)).Value
  For r = 2 To UBound(a)
    a(r, 1) = IIf(a(r, 1) < a(r, 2), a(r, 1) & a(r, 2), a(r, 2) & a(r, 1))
    d(a(r, 1)) = d(a(r, 1)) + 1
  Next r
  Application.ScreenUpdating = False
  For r = 2 To UBound(a)
    If d(a(r, 1)) > 1 Then Rows(r).Resize(, 2).Interior.ColorIndex = 45
  Next r
  Application.ScreenUpdating = True
End Sub