Compare Two column cells

[Imran/CVT]

Hello All,
I want to write a VBA macro to compare cells of column B and E. Comparing cells from row 3, 4 and 5 are easy but difficulty level is in row 6, 7 and 8. when comparing E6 to B6 i have to take a * value and this value could be A,B,C,D,E,F,G,H,J,L,M as highlighted in the figure. So if in B6 cell A (in red colour) is present instead of * so this means -F* in cell E6 could be -FA as A is the possible value of *.

Similarly the case in comparing B7 to E7 and, B8 to E8. I want to write a macro that do it automatically as data will be variable. Workbook is already attached. Help me with this Please! Thank you

[PCI]

How do you know which list to use to replace the *, is it due the letter before and after the * ??

[Imran/CVT]

How do you know which list to use to replace the *, is it due the letter before and after the * ??

I look for a red letter (after - F for example) in a corresponding cell of column A and then see if this letter can be replaced for *. And this possibility will be according to the list of letters highlighted in yellow

[Richard Buttrey]

Why do you show B4 & E4 as a mismatch? They look the same to me.

[Imran/CVT]

Why do you show B4 & E4 as a mismatch? They look the same to me.

My mistake sorry!

[PCI]

this possibility will be according to the list of letters highlighted in yellow

It means for each row where exists * there is a yellow list on the same row ???

[Imran/CVT]

It means for each row where exists * there is a yellow list on the same row ???

Yes, I can put if it helps to make program easy

[PCI]

Yes, I can put if it helps to make program easy

Wait, wait, the question is, does the yellow list linked to the * on the same row ?
In other words if a * is found where to search to get the characters list to replace it ?

[Richard Buttrey]

Maybe in F3 copied down

=IF(OR(E3=B3,IFERROR(SEARCH(MID(B3,SEARCH("~*",E3),1),G3),0)),"Match","Mismatch")

[Imran/CVT]

Hi Richard,
This formula is working correctly and doing the exact job.But I want to do it through VBA macro can you help me to write the code?

[Richard Buttrey]

Why not just use VBA to add that formula for you.
Assuming it's column A that determines the last cell with a value and hence how far down column F you want to copy then

Dim lRow As Long
lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Range("F3:F" & lRow) = "=IF(OR(E3=B3,IFERROR(SEARCH(MID(B3,SEARCH(""~*"",E3),1),G3),0)),""Match"",""Mismatch"")"

Sheet1 is the VBA sheet code name (NOT the tab name) change this as necessary

[Imran/CVT]

There is still one problem with the formula. With MID search it is also matching the letters other then in the place of *. In the attached pic one can see that it is also considering the before and after letters of * and gives wrong answer

[Imran/CVT]

Wait, wait, the question is, does the yellow list linked to the * on the same row ?
In other words if a * is found where to search to get the characters list to replace it ?

For example if a * is found in E6 Then we look in B6 which letter is in place of * in B6 then we we to yellow list and see if we can place this letter in E6. (here letter was A(red color) and we saw in the yellow list * could be equal to A)

[PCI]

See next code

Option Explicit

Sub Compare()
Dim WkList
Dim WkRg  As Range, Rg As Range
Dim T, TT
Dim WkVal As String

    Set WkRg = Range("A3:A" & Cells(Rows.Count, "A").End(3).Row).Resize(, 5)
    For Each Rg In WkRg.Columns(1).Cells
        If (Rg(1, 2) = Rg(1, 5)) Then
            Rg(1, 6) = "Match"
        Else
            T = InStr(Rg(1, 5), "*")
            If (T = 0) Then
                Rg(1, 6) = "Mismatch"
            Else
                WkList = Mid(Rg(1, 7), 5)
                Rg(1, 6) = "Mismatch"
                For Each TT In Split(WkList, ",")
                    WkVal = Rg(1, 5)
                    WkVal = Replace(WkVal, "*", TT)
                    If (Rg(1, 2) = WkVal) Then Rg(1, 6) = "Match": Exit For
                Next TT
            End If
        End If
    Next Rg
End Sub