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.AddUniqueValu es
.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