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 * ??

[Richard Buttrey]

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

[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

[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

Please Login or Register  to view this content.

[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)

[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

Please Login or Register  to view this content.

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

[PCI]

See next code

Please Login or Register  to view this content.