Forum Discussion
Compare cell value againt a string of text or data separated by comma in another cell
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.