Finding Duplicate in Long Numbers

[oam]

Is there a formula that can be put into Conditional Formatting that find and highlight duplicates in two adjacent columns of tracking numbers that are 36 digits long?

Example of tracking number length:

A	B
1239933700001234566900893489489863	1231002100008888888800893489549734
1239933700987456321000893489489863	1236414444444205596900893489653868
1239202800009201232580893489545682	1237750300009666666900893489665540
1237750300009208888880893489666028	1234303300005555556900893489596929

[jonh]

Sub Highlight_Duplicates()
    ClearAllHighlights
    getdups Range("a1:a4"), Range("b1:b4")
    getdups Range("b1:b4"), Range("a1:a4")
End Sub


Sub getdups(r1 As Range, r2 As Range)
    ar = Application.WorksheetFunction.Transpose(r2)
    Dim fnd As Range
    For Each fnd In r1
        If UBound(Filter(ar, fnd, True, vbTextCompare)) <> -1 Then highlight fnd
    Next
End Sub
Sub highlight(r As Range)
    With r.Interior
        .Pattern = xlSolid
        .Color = 65535
    End With
End Sub
Sub ClearAllHighlights()
    With ActiveSheet.Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

[snb]

Sub M_snb()
  With cells(1).currentregion.columns(1).resize(,2).FormatConditions.AddUniqueValues
    .DupeUnique = 1
    .Interior.Colorindex = 3
  End With
End Sub

[oam]

Is VBA my only option? Is there not a formula that can be put into the Conditional Formatting part of Excel to find duplicates with long numbers?

[snb]

yes there is.
Just open the option conditional formatting in the ribbon.
But running the macro will save you a lot of time.

[jonh]

...

[oam]

snb,

The macro does not examine any numbers past the 15th digit.

[oam]

jonh,

Your macro does what I asked for but I miss spoke what I realy needed when looking for duplicates, I need to find and highlight duplicates in two adjacent columns AND find duplicates within each column.


As you can see from my example, column A row 1 and 2 are duplicated numbers. In addition, column A row 3 and column B row 3 are duplicated numbers. I need the macro to look at both columns between then and in each column to ensure there are no duplicated numbers in either column.

A B 1239933700001234566900893489489863 1237750300009666666900893489665540 1239933700001234566900893489489863 1237750300009208888880893489666028 1236414444444205596900893489653868 1236414444444205596900893489653868

Sorry for any confusion

[snb]

Did you format the cells as text ?

[oam]

Columns A and B are formatted as text.

[oam]

In addition, with the use of Add Unique Values in the Conditional Formatting section I am finding that the formula in Conditional Formatting does not look past the 15th digit so not all the duplicate tracking numbers are not being detected.

[Kenneth Hobs]

Convert the numbers to text by typing a single quote and then pasting the number. Excel only has so much precision.

The standard conditional formatting rule for duplicates worked fine for me.

IF you still have problems, please attach a workbook.

[oam]

I came across this tracking number today and the standard Conditional Formatting section did not find it, the only difference between the two are the last two digits. As far as posting an example, I will have to sanitize a copy and remove the digital signature before I can upload a copy of the file, maybe tomorrow.
I as far as the numbers being converted to text, I added a macro to convert all items within the same range as checking for duplicates to prevent numbers from being entered by the users of the form.

Thank for your help

1238340600012345678900893490019158

1238340600012345678900893490019125

[oam]

I was working with "jonh" macro and this code is working to identify the duplicates but it is also highlighting the blank cells in the range, is there a way to get it to not identify the blank cells?

Thank you

[jonh]

If fnd <> "" then If UBound(Filter(ar, fnd, True, vbTextCompare)) <> -1 Then highlight fnd

[Kenneth Hobs]

Here is my find method.

Sub Test_FoundRanges()  Dim f As Range, ff As Range, r As Range
  Set r = Range("A1:B3")
  For Each f In r
    Set ff = FoundRanges(r, f.Value2)
    If Not ff Is Nothing And f.Value2 <> "" Then _
      If ff.Cells.Count >= 2 Then f.Interior.Color = vbRed
  Next f
End Sub




Function FoundRanges(fRange As Range, fStr As String) As Range
    Dim objFind As Range
    Dim rFound As Range, FirstAddress As String
     
    With fRange
        Set objFind = .Find(what:=fStr, After:=fRange.Cells((fRange.Rows.Count), fRange.Columns.Count), _
        LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=True)
        If Not objFind Is Nothing Then
            Set rFound = objFind
            FirstAddress = objFind.Address
            Do
                Set objFind = .FindNext(objFind)
                If Not objFind Is Nothing Then Set rFound = Union(objFind, rFound)
            Loop While Not objFind Is Nothing And objFind.Address <> FirstAddress
        End If
    End With
    Set FoundRanges = rFound
End Function

[oam]

Perfect!

Both codes work! Thank you all for being patient with me and helping me work through this problem.

Again thank you.

[snb]

Sub M_snb()
    sn = [index(3*N(countif(A1:B10,A1:B10)>1),)]
    
    For Each cl In [A1:B10]
       cl.Interior.ColorIndex = sn(cl.Row, cl.Column)
    Next
End Sub