Hi All,
i have updated the material code in Sheet2 column A2:A3 i want to search this code in sheet1 B2:B3 which is mingled in other details. if material code available need to show yes in remarks column
That's going to be difficult. You have the word Inch and the character " and in reversed order with MM and mm. Excel doesn't know that " means Inch.
I would say you need to clean the data in sheet 2 first. Replace " by "Inch", upper case all, swap inches and mm's so that you get consistent data in both sets. Still you need to deal with exceptions. Why does " - B9044210005000090-FSF (I)" not matter for the first item, but why does " -FSF(I)" matter for the 2nd??
Power Query and fuzzy matching could be a way out but perhaps not.
Hi Riny
I have updated the material codes in column A2:A3 of Sheet2 and would like to check if these codes exist in column B2:B3 of Sheet1.
Could you please help me with a formula to achieve this? I would like to update column C2 of Sheet2 with "Yes" if the code is found in Sheet1 and "No" if it is not found.
@navsal66 Still not sure if I fully understand but in the attached file I used PQ Fuzzy Matching to merge the two tables based on the description columns.
At the default 'fuzziness' of 80% only the first rows are matched. Reduce it to 65% (i.e. 0.65) and the 2nd one is matched as well.
See if you can get this to work on your end. come back here if you get stuck.
@navsal66 You have lost me. Two tables with similar "Name" and "CJ description" that need to be matched somehow. And you want to return something in Sheet 2 in the "Remarks" column".
Please complete Sheet 2 for me with whatever you need in the "Remarks" column. Then explain in words what the logic behind it is. Pictures and arrows just don't mean much to me. Sorry!
@navsal66 Thanks for clarifying. I believe you need the following formula in I2 and copy it down.
=IF(ISNUMBER(SEARCH(G2,B2)),"YES","NO")
Thanks Riny its working fine. and thanks for your support