PDA

View Full Version : [SOLVED] Finding Duplicate in Long Numbers



oam
06-18-2015, 07:33 PM
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
06-19-2015, 02:32 AM
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
06-19-2015, 02:51 AM
Sub M_snb()
With cells(1).currentregion.columns(1).resize(,2).FormatConditions.AddUniqueValu es
.DupeUnique = 1
.Interior.Colorindex = 3
End With
End Sub

oam
06-19-2015, 01:08 PM
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
06-19-2015, 01:27 PM
yes there is.
Just open the option conditional formatting in the ribbon.
But running the macro will save you a lot of time.

jonh
06-19-2015, 01:38 PM
...

oam
06-19-2015, 02:19 PM
snb,

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

oam
06-19-2015, 02:48 PM
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
06-20-2015, 03:58 AM
Did you format the cells as text ?

oam
06-22-2015, 04:15 PM
Columns A and B are formatted as text.

oam
06-22-2015, 05:29 PM
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
06-22-2015, 08:03 PM
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
06-23-2015, 03:52 PM
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
06-23-2015, 04:54 PM
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
06-24-2015, 10:17 AM
If fnd <> "" then If UBound(Filter(ar, fnd, True, vbTextCompare)) <> -1 Then highlight fnd

Kenneth Hobs
06-24-2015, 11:26 AM
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
06-24-2015, 02:25 PM
Perfect!

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

Again thank you.

snb
06-24-2015, 02:58 PM
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