Matching cells and color the matching cells

[Guzzlr]

Range("B:B,D:D").Select
    Range("D1").Activate
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Hello All.
I ran the recorder to find duplicate values and color based upon a conditional. This is not the way I really wanted.
Problem: I have letters/numbers in column B. I wish to match the column B with letters/numbers in column D. However, it is not an exact match.
For example, the letter/number in column B may be ACI-5, which almost matches ACI-5(G) in column D.
Because it is an approximate match, both cells in Column B and Column D will be colored RGB 248, 56, 132.
I originally thought about using a vlookup application, but I don't believe that is the way to go.
Any thoughts would be greatly appreciated.
Thanks for the help

 

[RileyC]

How close do you want the match to be? Have you used fuzzy comparison? You can look at the character difference it takes to turn one string to the next and set how much it can be different.

 

[Guzzlr]

How close do you want the match to be? Have you used fuzzy comparison? You can look at the character difference it takes to turn one string to the next and set how much it can be different.

that's a new term I've not heard of...Fuzzy Comparison

 

[Guzzlr]

How close do you want the match to be? Have you used fuzzy comparison? You can look at the character difference it takes to turn one string to the next and set how much it can be different.

So far, it looks like I am looking at 3 types of Fuzzy Matches:
1. ABC-20 Would match to ABC-20(K). So this match would be fairly accurate.
2. ABCD 2164 would match to 1V-45N-2164. So this match would not be accurate, and key on the last digits. However, the last 4 digits may read 2160-3, as an example
3. LC5-DHJ-005 (P) would match to LC5-DHJ-005, so this 3rd match would be accurate

I believe that is what you were asking?

 

[RileyC]

Yeah its similar. Depending on what you're looking for I would maybe look at breaking it up into a matrix with the spaces in each word and comparing the character difference between each string that's broken up, then using an assignment problem to see how many characters each string differs by when broken up with and matched with the closest string.

I.e. between ABCD 2164 and LC5-DHJ-05 would look like

Matrix A:
Abcd 2164
Lc5-dhj-05 4 4
Blank string 4 4

hungarianAlgorithm(A) = (1,1)(4), (2,2)(4)
See my first topic on string comparison, I made a similar algorithm for address comparison as they differed slightly such as PLACE and PL and it worked relatively well however could be slow depending on how many you're running it for (can speed up with python and other scripting languages.) Run time is O(n^4) or O(n^5) for each string you compare together but it's extremely accurate once you configure it correctly.

 

[Guzzlr]

Yep...I'm lost on this one, sorry

 

[RileyC]

If you wanna post your code I can go over it when I get home or I'll explain more in depth what I mean here its hard to format on my phone apologies.

 

[Guzzlr]

If you wanna post your code I can go over it when I get home or I'll explain more in depth what I mean here its hard to format on my phone apologies.

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And sht.Name = "Template" Then
    sht.Activate
    End If
Next sht

LastRow = Range("B:E").Cells.Find("*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox " The Last Non-Blank Row Is " & LastRow

'***************** Standardize EFG Team ******************
'With Sheets("Template")
'For Each cl In Range("B7", Range("B" & Rows.Count).End(xlUp))
'   cl.Value = Split(cl.Value, " /")(0)
'Next cl
'End With

For Each cl In Range("B7", Range("B" & Rows.Count).End(xlUp))
If InStr(1, cl.Value, " /", 0) > 0 Then
   cl.Value = Split(cl.Value, " /")(0)
   End If
Next cl
MsgBox " Stop "

'***************** Standardize ABCD Number ****************
For Each cl In Range("D7", Range("D" & Rows.Count).End(xlUp))
If InStr(1, cl.Value, "ABCD ", 1) > 0 Then
   cl.Value = Split(cl.Value, "TCTO ")(1)
   End If
Next cl



'******************* v Lookup Area **********************
With Sheets("Template")
    If Application.WorksheetFunction.VLookup(Sheets("Template").Range("B7:B100"), _
        Sheets("Template").Range("B7:E1000"), 3, True) Then
        Cells(i, "B").Interior.Color = RGB(216, 228, 188) And Cells(i, "D").Interior.Color = RGB(216, 228, 188)
    End If
End With
End Sub

So, I don't know if this will help. My thought was I was going to use a v-lookup strategy on a non-exact match to find the matches, and then color the cells. So, I began with code that would standardize the areas so the v-lookup would get close...but this probably would not work, because of the amount of variation, for example: If I v-lookup AB-001-55, V-lookup will come up with AB-001-5, instead of AB-001-55.

 

[Guzzlr]

Yeah its similar. Depending on what you're looking for I would maybe look at breaking it up into a matrix with the spaces in each word and comparing the character difference between each string that's broken up, then using an assignment problem to see how many characters each string differs by when broken up with and matched with the closest string.

I.e. between ABCD 2164 and LC5-DHJ-05 would look like

Matrix A:
Abcd 2164
Lc5-dhj-05 4 4
Blank string 4 4

hungarianAlgorithm(A) = (1,1)(4), (2,2)(4)
See my first topic on string comparison, I made a similar algorithm for address comparison as they differed slightly such as PLACE and PL and it worked relatively well however could be slow depending on how many you're running it for (can speed up with python and other scripting languages.) Run time is O(n^4) or O(n^5) for each string you compare together but it's extremely accurate once you configure it correctly.

So getting back to this topic, I did a little research on fuzzyvlookup.
lets say i have a data set with 300 rows.
The lookup value in C7 is Lc5-dhj-0544
Lets say in E47, i have XXXX 0544
D47, has the contents I wish to pull, when XXXX 0544 is found (keying on the 0544)
of course, this will go row-by-row, beginning in C7
When I tried an approximate match, with a vlookup, It may find 0544, or it may find 0544-001, or it may find 0544(J)
Will a fuzzy vlookup work here row-by-row on a loop?
Thanks for the help

 

[RileyC]

It wouldn't necessarily. If that's the case I would break up each row string with a space between the words and compare each value with what you are searching for. I.e. matching 5044 with xxxx 5044 would break up as xxxx, 5044. Then it would find a match on 5044. With 5044-001 it wouldn't match since it isn't broken up. You could also write a function that goes through EVERY value and finds the closest match but depending on how many rows you have it will run very slow after ~6000 of them. (36000000 comparisons).