Compare comma separated values in a cell to a list

**SMB** · 01-29-2013, 01:06 PM

Hello everyone,

I have been trying to figure out a way to compare a cell's content to a list, and if all the cell's content matches the list, return a True/False. I was hoping to avoid the text to columns solution if possible.

For example (and what the answer would be):

Cell Values
1,2,3 (True)
2,3,4 (False)
3,4,5 (False)
4,5,6 (False)
5,6,7 (True)
6,7,8 (True)
7,8,9 (True)

List
1,2,3,5,6,7,8,9

The cells have varying lengths, however. Some are only 2 values, through 162 values.

Thanks for any help you can provide in advance!

-SMB

**Fotis1991** · 01-29-2013, 01:24 PM

I don't like to be a "smart..." quy so 2 excellent solutions exist here.

http://www.mrexcel.com/forum/excel-q...es-column.html

**bebo021999** · 01-29-2013, 02:00 PM

Assuming your list is in A1:A8; cell value is in C1:C7
1- Replace C1 with each list item in A1:A8, then count number of characters of C1 after replacing with each items of list
LEN(SUBSTITUTE(C1,$A$1:$A$8,""))
2- Comparing with character numbers of C1, to count how many items has been replaced:
SUMPRODUCT(--(LEN(SUBSTITUTE(C1,$A$1:$A$8,""))<LEN(C1)))
3- Finally, comparing 2 with character number of C1
In D1:
=SUMPRODUCT(--(LEN(SUBSTITUTE(C1,$A$1:$A$8,""))<LEN(C1)))=LEN(SUBSTITUTE(C1,",",""))
Drag down

**Teethless mama** · 01-29-2013, 02:21 PM

See the attached

**SMB** · 01-29-2013, 02:43 PM

Hello all - thanks for the answers!

Fotis1991, not sure what makes you a smart... there, but the solution you presented worked. I think there's a lot of this information available online, but it's just a matter of search terms, and apparently I didn't use the correct terms there. Thanks again!

**Fotis1991** · 01-29-2013, 02:52 PM

You are welcome and thank you for the reb*. My English are not good so maybe it's wrong what i said. So, one more try to explain.

What i meaned is that i don't want to send the solution, giving the idea that i created this. So it is not my code or formula.

I had this link to my file. I didn't search now for this.

**Elasar** · 01-30-2013, 06:51 PM

Originally Posted by SMB

Hello everyone,

I have been trying to figure out a way to compare a cell's content to a list, and if all the cell's content matches the list, return a True/False. I was hoping to avoid the text to columns solution if possible.

For example (and what the answer would be):

Cell Values
1,2,3 (True)
2,3,4 (False)
3,4,5 (False)
4,5,6 (False)
5,6,7 (True)
6,7,8 (True)
7,8,9 (True)

List
1,2,3,5,6,7,8,9

The cells have varying lengths, however. Some are only 2 values, through 162 values.

Thanks for any help you can provide in advance!

-SMB

=IF(ISERROR(FIND(H3:H9,$H$11,LEFT(H3:H9,1))=1),FALSE,TRUE) this array formula (ctrl+shift+entr) works, it is pointless to mention that the ranges should be changed accordingly(i just copied your columnar data to h3:h9 ?(surely cleaned from true false) and full list ti h11

**dell001** · 10-02-2019, 07:16 AM

Dear,

Could someone help me please, I have column with number must be equivalent to another column value.

Number value
1 100
2 50
3 210
4 320
5 500
. .
. .
. .
1000 2

and I have a my sheet having a column with some cell could having more than one numbers separted with comma.

column somme
5 500
2 50
5,3 this should be (210+500)
.
.
.

I need to generate a new somme column, need you help please.

**FDibbins** · 10-03-2019, 01:10 AM

Originally Posted by dell001

Dear,

Could someone help me please, I have column with number must be equivalent to another column value.

I need to generate a new somme column, need you help please.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread