Searching for values in cells that contain multiple values per cell

[konsic19]

I have a column of data (Column A) that has multiple values per row that are delimited by a comma. I’m trying to find a way to search this column to find rows that have two or more codes from another column, Column B (single values in this column). Any ideas on how I can do this? My second question, which is more difficult if even possible, is to do a search of Column A to find rows that contain at least one value from Column B and at least one value from Column C. Any thoughts on this?

Image below:

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[FDibbins]

Post some sample data here please, I am not allowed to use file-hosting sites - also, we cannot really work with pics

 

[konsic19]

Post some sample data here please, I am not allowed to use file-hosting sites - also, we cannot really work with pics

Column A B C

58270,80048,81003,82962 <colgroup><col width="64" span="3" style="width: 48pt;"></colgroup><tbody> </tbody>	82962	80048
71020,74020,74178,80048	41234	42342
52005,71020,77002,80048 <tbody> </tbody>	70490 <tbody> </tbody>	43242 <tbody> </tbody>
70551,71020,80053,84443,85025 <tbody> </tbody>	73200	13453

<tbody>
</tbody>

 

[FDibbins]

Will this help?

	A​	B​	C​	D​	E​
2​	58270,80048,81003,82962	82962​		82962​	80048​
3​	71020,74020,74178,80048	80048​		41234​	42342​
4​	52005,71020,77002,80048, 73200	73200​		70490​	43242​
5​	70551,71020,80053,84443,85025			73200​	13453​

B2=IFERROR(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$5,A2),$D$2:$D$5),LOOKUP(9.99999999999999E+307,SEARCH($E$2:$E$5,A2),$E$2:$E$5)),"")
copied down

This is searching both columns and returning the 1st match it finds

 

[konsic19]

Thank you! So I was looking to find rows in Column A that include 1 code from column B and one from Column C, but I was able to use your formula and split it up into two and run this formula in two columns and then look for columns where both fields are populated.

Is there a way to search for rows in Column A that contain two or modes from another column (e.g. Column B)?

 

[FDibbins]

Thank you! So I was looking to find rows in Column A that include 1 code from column B and one from Column C, but I was able to use your formula and split it up into two and run this formula in two columns and then look for columns where both fields are populated.

Yup, I figured that's what you might want to do, that's why I put them both in there

Is there a way to search for rows in Column A that contain two or modes from another column (e.g. Column B)?

Not sure what you mean here, can you explain please (and maybe show some samples?)

 

[konsic19]

Sorry for the typo. Is there a way to search for rows in Column A that contain two or more codes from another column (e.g. Column B)?

For example, Row A2 has 2 or more values from Column B, and I would like to find a way to find such instances.

Column A	Column B
58270,80048,81003,82962	71020
71020,74020,74178,80048	80076
71040,74220,74178,80048	74020

<tbody>
</tbody>

Thanks for your help!

 

[FDibbins]

So what would a sample answer look like?

	A​	B​	C​	D​
1​	58270,80048,81003,82962	71020		2​
2​	71020,74020,74178,80048	80076
3​	71040,74220,74178,80048	74020

D1=SUM(COUNTIF($A$1:$A$3,"*"&$B$1:$B$3&"*"))
ARRAY entered using CTRL SHIFT ENTER, not just enter

 

[konsic19]

Perfect! Thanks Ford, appreciate it!

Konrad

 

[FDibbins]

Awesome, happy to help