Feb 10 2022 02:00 PM
I have 2 lists of data in 2 different columns as "Set 1" and "Set 2". The value in cell (A2) under "Set 1" need to be compared with text in cell (B2) under "Set 2" and it can have comma separated values to be compared against and can also have duplicates values in it. I would like to find matching value for A2 in B2 (First one if there are duplicates) under "Set 2" and return it under "Match" i.e Column C and apply the same condition for the rest of the rows
Feb 10 2022 02:04 PM - edited Feb 10 2022 02:04 PM
Feb 13 2022 09:29 PM
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.
Feb 14 2022 04:27 AM
SolutionFeb 14 2022 04:52 AM
Thanks much. That worked like a magic
Mar 21 2022 11:57 AM
@Hans VogelaarI 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 |
Mar 21 2022 12:10 PM
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.
Feb 14 2022 04:27 AM
SolutionSorry change it to
=IF(ISNUMBER(FIND(", "&A2&", ",", "&B2&", ")),A2,"")