Populate With Approximate Match

[kumara_faith]

Hi,

I have the following table:

Book1
	B	C	D
1	Interaction	Case ID	Match
2	Case ID: 554878 service recovery completed	554868	Yes
3	Case ID 554879 service recovery completed	555823	Yes
4	Case ID554899 service recovery completed	554812	No
5	Case ID 555823 service recovery completed	554878	Yes
Sheet1

In column D , I am trying to match the case ID from column C to column B.

Criteria:
If the case ID matches exactly, then return Yes
If the case ID does not match by one digit but the word recovery and service exist in the same cell, then return Yes
If the case ID does not match by more than one digit even though the word service and recoverey exist in the same cell, return No

The correct answer is in column D.

Is this possible with a formula solution ? Appreciate all the help.

 

[etaf]

If the case ID does not match by one digit but the word recovery and service exist in the same cell, then return Yes

Can you just expand on the digit
would 654878 = 554878 - as 1 digit out

If the case ID does not match by more than one digit

so with that same example
would 564878 & 544878 = 554878 - as 1 digit out, as the 2nd digit is 5 - so 6 and 4 is only 1 out in 2nd digit, BUT 4 is 1 digit LESS so no math

may need some examples that do match and some that do not and why, as confused on that

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available

 

[kumara_faith]

Hi,

Actually it could be any combination of 1 digit wrong. I have an updated table as below:

Book2.xlsx
	B	C	D
1	Interaction	Case ID	Match
2	Case ID: CC_55487887 service recovery completed	CC_55487887	Yes
3	Case ID CC_55487958 service recovery completed	CC_55582345	Yes
4	Case IDCC_55489911 service recovery completed	CC_55489912	Yes
5	Case ID CC_55582345 service recovery completed	CC_55487958	Yes
6	Case IDCC_55489922 service recovery completed	CC_55489933	No
Sheet1 (2)

 

[etaf]

not sure i can help with this question

 

[Sam_D_Ben]

@etaf, sorry to intervene
@kumara_faith, If you're comfortable in VBA, would you mind checking if this code works as expected

Book1
	A	B	C
1	Interaction	Case ID	Match
2	Case ID: 554878 service recovery completed	554868	Yes
3	Case ID 554879 service recovery completed	555823	Yes
4	Case ID554899 service recovery completed	554812	No
5	Case ID 555823 service recovery completed	554878	Yes
6	Case ID: CC_55487887 service recovery completed	CC_55487887	Yes
7	Case ID CC_55487958 service recovery completed	CC_55582345	Yes
8	Case IDCC_55489911 service recovery completed	CC_55489912	Yes
9	Case ID CC_55582345 service recovery completed	CC_55487958	Yes
10	Case IDCC_55489922 service recovery completed	CC_55489933	No
Sheet1

Sub MatchCaseIds()
    Dim x2 As Worksheet
    Dim x3 As Long, x4 As Long
    Dim x5 As Long, x6 As Long
    Dim x7 As String, x8 As String
    Dim x9 As String
    Dim x10 As Boolean
    Dim x11 As Integer
    
    Set x2 = ActiveSheet
    
    x3 = x2.Cells(Rows.count, 1).End(xlUp).Row
    x4 = x2.Cells(Rows.count, 2).End(xlUp).Row
    
    For x5 = 2 To x4
        x8 = x12(x2.Cells(x5, 2).Value)
        x10 = False
        
        For x6 = 2 To x3
            x9 = LCase(x2.Cells(x6, 1).Value)
            x7 = x12(x9)
            
            If x7 = x8 Then
                x2.Cells(x5, 3).Value = "Yes"
                x10 = True
                Exit For
            End If
            
            If InStr(x9, "service") > 0 And InStr(x9, "recovery") > 0 Then
                x11 = x13(x7, x8)
                If x11 = 1 Then
                    x2.Cells(x5, 3).Value = "Yes"
                    x10 = True
                    Exit For
                End If
            End If
        Next x6
        
        If Not x10 Then x2.Cells(x5, 3).Value = "No"
    Next x5
    
    
End Sub

Function x12(x14 As String) As String
    Dim x15 As Integer, x16 As String
    x16 = ""
    
    For x15 = 1 To Len(x14)
        If Mid(x14, x15, 1) Like "[0-9]" Then
            x16 = x16 & Mid(x14, x15, 1)
        End If
    Next x15
    
    x12 = x16
End Function

Function x13(x17 As String, x18 As String) As Integer
    Dim x19 As Integer, x20 As Integer
    
    If Len(x17) <> Len(x18) Then
        x13 = 99
        Exit Function
    End If
    
    x20 = 0
    For x19 = 1 To Len(x17)
        If Mid(x17, x19, 1) <> Mid(x18, x19, 1) Then x20 = x20 + 1
    Next x19
    
    x13 = x20
End Function