SOLVED

Compare cell value againt a string of text or data separated by comma in another cell

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3151980%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECompare%20cell%20value%20againt%20a%20string%20of%20text%20or%20data%20separated%20by%20comma%20in%20another%20cell%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3151980%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%202%20lists%20of%20data%20in%202%20different%20columns%20as%20%22Set%201%22%20and%20%22Set%202%22.%20The%20value%20in%20cell%20(A2)%20under%20%22Set%201%22%20need%20to%20be%20compared%20with%20text%20in%20cell%20(B2)%20under%20%22Set%202%22%20and%20it%20can%20have%20comma%20separated%20values%20to%20be%20compared%20against%20and%20can%20also%20have%20duplicates%20values%20in%20it.%20I%20would%20like%20to%20find%20matching%20value%20for%20A2%20in%20B2%20(First%20one%20if%20there%20are%20duplicates)%20under%20%22Set%202%22%20and%20return%20it%20under%20%22Match%22%20i.e%20Column%20C%20and%20apply%20the%20same%20condition%20for%20the%20rest%20of%20the%20rows%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F347152i5F3035B950A58BCD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22vijay_kiran_2-1644529631651.png%22%20alt%3D%22%5C%26quot%3Bvijay_kiran_2-1644529631651.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3151980%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

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

 

vijay_kiran_2-1644529631651.png

 

 

6 Replies

@vijay_kiran

In B2:

 

=IF(ISNUMBER(FIND(","&A2&",",","&B2&",")),A2,"")

 

Fill down.

@Hans Vogelaar 

 

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.

best response confirmed by vijay_kiran (New Contributor)
Solution

@vijay_kiran 

Sorry change it to

 

=IF(ISNUMBER(FIND(", "&A2&", ",", "&B2&", ")),A2,"")

@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.

 

vijay_kiran_1-1647888878352.png

 

Set 1Set 2Missing
PB, ZA, ZMAK, 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, ZSPB
ZAAK, 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, HRDL, GJ, KA, MH, PB, PC, PD, PF, PH, TG, UP, ZA, ZB, ZC, ZE, ZF, ZG, ZH, ZJ, ZK, ZM, ZN, ZP, ZRHR
AK, KA, PD, ZAGJ, GT, HR, MH, PB, PC, PE, PF, PH, PJ, TG, TN, UP, ZA, ZB, ZC, ZE, ZH, ZJ, ZK, ZM, ZN, ZP, ZRAK, KA, PD
DA, DL, HR, KA, PB, PC, PD, PF, PH, ZA, ZB, ZC, ZJ, ZK, ZM, ZN, ZPDL, HR, KA, MH, PB, PC, PD, PE, PF, PH, UP, ZA, ZB, ZC, ZE, ZG, ZK, ZM, ZN, ZPDA, ZJ
PBDL, GJ, HR, KA, MH, PA, PC, PD, PF, PH, UP, ZA, ZB, ZC, ZE, ZH, ZJ, ZK, ZM, ZN, ZP, ZR 
PB, ZJPB, ZJ 

@vijay_kiran 

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.