+ Reply to Thread
Results 1 to 9 of 9

Compare comma separated values in a cell to a list

Hybrid View

  1. 01-29-2013, 01:06 PM #1
    SMB
    SMB is offline
    Registered User
    Join Date
    12-09-2009
    Location
    Palatine, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    30

    Compare comma separated values in a cell to a list

    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
    Reply With QuoteRegister To Reply
  2. 01-29-2013, 01:24 PM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Compare comma separated values in a cell to a list

    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
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 01-29-2013, 02:00 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,645

    Re: Compare comma separated values in a cell to a list

    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
    Quang PT
    Reply With QuoteRegister To Reply
  4. 01-29-2013, 02:21 PM #4
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Compare comma separated values in a cell to a list

    See the attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-29-2013, 02:43 PM #5
    SMB
    SMB is offline
    Registered User
    Join Date
    12-09-2009
    Location
    Palatine, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Compare comma separated values in a cell to a list

    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!
    Reply With QuoteRegister To Reply
  6. 01-29-2013, 02:52 PM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Compare comma separated values in a cell to a list

    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.
    Reply With QuoteRegister To Reply
  7. 01-30-2013, 06:51 PM #7
    Elasar
    Elasar is offline
    Registered User
    Join Date
    12-23-2012
    Location
    Azerbaijan
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Compare comma separated values in a cell to a list

    Quote Originally Posted by SMB View Post
    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
    Reply With QuoteRegister To Reply
  8. 10-02-2019, 07:16 AM #8
    dell001
    dell001 is offline
    Registered User
    Join Date
    02-20-2018
    Location
    LEEDS
    MS-Off Ver
    2010
    Posts
    25

    Re: Compare comma separated values in a cell to a list

    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.
    Reply With QuoteRegister To Reply
  9. 10-03-2019, 01:10 AM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Compare comma separated values in a cell to a list

    Quote Originally Posted by dell001 View Post
    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1