Forum Discussion
Compare cell value againt a string of text or data separated by comma in another cell
Thank you, that is almost what I was looking for. I believe there a small tweaking required though. Its not picking the value if it is not the first one in the cell. Just to clarify from my example, in row 2, its not picking PB and giving a blank as that was not the first one in the cell but its helped in row 3 cause PB is the first value in it and returning it. Same problem in row 4 & 5 and working in row 6 again and so on.
Thanks much. That worked like a magic
HansVogelaarI was wondering if you can help & suggest on similar query once again, but this time with 1 change in the data and a another change in the desired outcome.
Data change: If I have more than 1 value in Set 1 (i.e again separated by a comma (, ) and I wanted compare all the values in Set 1 with all the values in Set 2.
Desired Outcome: After comparison of Set 1 with Set 2, wanted see the missing values from Set 2 in Column C (As shown in the pic). Could this be possible? I have tried with the same formula to see if I can find all the matching values in Column C, few showed accurately and some were missing that should have showed up as a match. So, I was wondering to find the missing ones this time to make it easy to see the difference as my data is bit larger than the one from the example below (Not sure if the text is clear to view.
Set 1 Set 2 Missing PB, ZA, ZM AK, DL, GJ, GT, HM, HR, KA, LD, MH, PA, PC, PD, PE, PF, PG, PH, PJ, RH, RJ, TG, TN, UP, WB, XU, ZA, ZB, ZC, ZD, ZE, ZF, ZG, ZH, ZJ, ZK, ZM, ZN, ZP, ZR, ZS PB ZA AK, DL, GT, HM, HR, JH, KA, KL, MH, PB, PC, PD, PE, PF, PG, PH, PJ, RH, TG, TN, UP, WB, XU, ZA, ZB, ZC, ZE, ZF, ZG, ZH, ZJ, ZK, ZL, ZM, ZN, ZP, ZR, ZS PB, HR DL, GJ, KA, MH, PB, PC, PD, PF, PH, TG, UP, ZA, ZB, ZC, ZE, ZF, ZG, ZH, ZJ, ZK, ZM, ZN, ZP, ZR HR AK, KA, PD, ZA GJ, GT, HR, MH, PB, PC, PE, PF, PH, PJ, TG, TN, UP, ZA, ZB, ZC, ZE, ZH, ZJ, ZK, ZM, ZN, ZP, ZR AK, KA, PD DA, DL, HR, KA, PB, PC, PD, PF, PH, ZA, ZB, ZC, ZJ, ZK, ZM, ZN, ZP DL, HR, KA, MH, PB, PC, PD, PE, PF, PH, UP, ZA, ZB, ZC, ZE, ZG, ZK, ZM, ZN, ZP DA, ZJ PB DL, GJ, HR, KA, MH, PA, PC, PD, PF, PH, UP, ZA, ZB, ZC, ZE, ZH, ZJ, ZK, ZM, ZN, ZP, ZR PB, ZJ PB, ZJ I get PB for the next to last one...
Here is a solution using a custom VBA function; you'll have to allow macros when you open the workbook.