PDA

View Full Version : Highlighting Duplicates in Excel that are not a perfect match



bob_hughes
03-26-2015, 06:06 AM
Hey guys so I have been working on trying to figure this coding out for a couple days and it is driving me nuts :banghead: I could not quite think of how to word my question, but I did find a post on another forum from another guy who has essentially the same issue, only mine involves three columns of about 8000 rows. The only answer he received referred him to this forum so hopefully I can get some help! Thanks :D

=========================================================================== =============================================================

I have a list of names in three columns - B2:B425, D2:406, & E2:30 where they were input by different people, so the names are worded differently. For example, I can have the name "Handler, Jones, & Wright" and someone else has the name listed as "Handler Corp." What I need is to find a formula or VBA macro code that can search through my list and notice the possible duplicates and highlight them. Since they are all different names, I cannot give it a unique "text" to search.

I found a code posted in this forum from some time ago (for two columns) but it highlighted all these names that had no partial words in common. Perhaps you can look over the code below and modify it for me or provide me with another one? Please let me know if you need any further information to guide me.



Sub HighlightDups()
Dim rg1 As Range, rg2 As Range, c As Range, d As Range
Dim sTemp As String, sTempWords() As String, sTempDWords() As String
Dim re As Object, mc As Object
Dim i As Long, j As Long
Dim sFirstAddress As String

Set rg1 = Range("B2", Cells(Rows.Columns.Count, "B").End(xlUp))
Set rg2 = Range("D2", Cells(Rows.Columns.Count, "D").End(xlUp))

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.ignorecase = True

With Range(rg1, rg2)
.Font.Color = vbBlack
.Font.Bold = False
.Interior.Color = xlNone
.FormatConditions.Delete
End With

For Each c In rg1
re.Pattern = "\b\w+\b"
If re.test(c.Text) = True Then
Set mc = re.Execute(c.Text)
ReDim sTempWords(0 To mc.Count - 1)
For i = 0 To UBound(sTempWords)
sTempWords(i) = mc(i)
Next i

For i = 0 To UBound(sTempWords)
Set d = rg2.Find(What:=sTempWords(i), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not d Is Nothing Then
re.Pattern = "\b" & sTempWords(i) & "\b"
sFirstAddress = d.Address
Do

If re.test(d.Text) Then
With c
.Font.Color = vbWhite
.Font.Bold = True
.Interior.Color = vbBlue
End With
With d
.Font.Color = vbWhite
.Font.Bold = True
.Interior.Color = vbBlue
End With
End If

Set d = rg2.FindNext(after:=d)
Loop While Not d Is Nothing And d.Address <> sFirstAddress
End If
Next i
End If
Next c
Set re = Nothing
End Sub

snb
03-26-2015, 06:34 AM
I'd prefer a sample workbook to an untagged code.
Are you familliar with 'autofilter' in Excel ?

bob_hughes
03-26-2015, 06:56 AM
No I am not very familiar with autofilter, however I am a quick learner. And Also just to give you sort of an idea of what I am looking for here an attached photo/description.
======================================

Essentially What I have Been Doing is Running the stock Duplicate Values conditional formatting formula and this is what comes up. As you can see CAPRO-Hungary Gets Flagged But no other CAPRO does. Comparatively the same thing happens with ASCOTEC. However If I were to run this I would need every single CAPRO or ASCOTEC to be highlighted. The same would go for any other company from Column R and S that might match with column T. I can provide a workbook if this is not enough, just let me know! Also I can't tell if this photo will enlarge or not so I will paste the link to the photo as well.



13070

snb
03-26-2015, 07:30 AM
I'd prefer a sample workbook to a picture (after all this is not a Photoshop forum).

bob_hughes
03-26-2015, 08:15 AM
Okay my test workbook should be attached. Essentially what I need is to run the Vendor List against both the Denied Party List, as Well as the Denied Party Alternate List. Additionally I want it so that if there is a match between them then the Name in the Vendors List Gets Highlighted. Finally, I need it so at the very least if the first 4 letters are the same in the vendor list as a name in the Denied Party lists it highlights.

I would also possibly interested in that if a sequence of say 4-5 characters with in each cell of the Vendors List matches 4-5 consecutive letters/characters in the Denied list it gets highlighted, however I am not 1000% sure that can be done or not. Thanks for any and all help!

bob_hughes
04-01-2015, 06:16 AM
any luck with this?