+ Reply to Thread
Results 1 to 14 of 14

Need formula for comparing items in one column to another.

  1. #1
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Need formula for comparing items in one column to another.

    I need a formula that will tell me if every item in one column range is somewhere in a second column. The following formula tells me if one item (A100 in this case) is in a column. =SUMPRODUCT(--($A100=$A$10:$A99))>0 I tried to do something like =SUMPRODUCT(--($A150:$A170=$A$10:$A99))>0 but that doesn't work. TIA
    Attached Files Attached Files
    Last edited by bird333; 06-13-2020 at 08:45 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,039

    Re: Need formula for comparing items in one column to another.

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by CARACALLA View Post
    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Added attachment.

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,039

    Re: Need formula for comparing items in one column to another.

    B2=IF(ISNUMBER(MATCH(A2,$D$2:$D$6,0)),"match ","no match ")

    Copy down


    C2=IFERROR(MATCH(A2,$D$2:$D$6,0),"no match")

    copy down
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by CARACALLA View Post
    B2=IF(ISNUMBER(MATCH(A2,$D$2:$D$6,0)),"match ","no match ")

    Copy down


    C2=IFERROR(MATCH(A2,$D$2:$D$6,0),"no match")

    copy down
    Perhaps I wasn't clear. I can do that with my original formula (i.e. copy it down the spreadsheet). I want one formula in one cell that examines the ranges and spits out a True or False (really I want a 1 or 0).

  6. #6
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    67

    Re: Need formula for comparing items in one column to another.

    G4
    Please Login or Register  to view this content.
    Row row row your boat
    Gently down the stream

  7. #7
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by Metoo7 View Post
    G4
    =sumproduct(--(countif(d2:d6,a2:a4)=0))=0
    Thanks! This works. Do you mind explaining how this works?

  8. #8
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    67

    Re: Need formula for comparing items in one column to another.

    Please see my screenshot explanation:
    Attachment 682330

  9. #9
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by Metoo7 View Post
    Please see my screenshot explanation:
    Attachment 682330
    Sorry but it says 'invalid attachment'.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,964

    Re: Need formula for comparing items in one column to another.

    =sumproduct(--(countif(d2:d6,a2:a4)=0))=0

    Search (by counting) each value in cell A2,A3,A4 in range D2:D6, if found, count 1
    if any cell found, sumproduct>0=>FALSE
    if nothing found, sumproduct=0=>TRUE

  11. #11
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    67

    Re: Need formula for comparing items in one column to another.

    how about now:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by bebo021999 View Post
    =sumproduct(--(countif(d2:d6,a2:a4)=0))=0

    Search (by counting) each value in cell A2,A3,A4 in range D2:D6, if found, count 1
    if any cell found, sumproduct>0=>FALSE
    if nothing found, sumproduct=0=>TRUE
    Thanks for the reply.

  13. #13
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Quote Originally Posted by Metoo7 View Post
    how about now:
    Please Login or Register  to view this content.
    Yes I can see that now. Thanks again.

  14. #14
    Registered User
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2010, 2013, 2016
    Posts
    78

    Re: Need formula for comparing items in one column to another.

    Is there a way to tweak this formula to ignore blank cells in the 'a2:a4' range? Or at least make them '1' so they are checked if they are 0 (of course they wouldn't be)? This formula fails even when there is a blank cell in the "d2:d6" range that should match the blank cell in the "a2:a4" range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Unique items in column C when comparing B to A
    By Timja in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2020, 08:39 AM
  2. [SOLVED] Comparing items within 2 dictionaries
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-25-2018, 07:12 AM
  3. [SOLVED] Comparing lists and keeping only like items
    By moxiegirl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2015, 11:48 PM
  4. Comparing Long List Of Items against Each Other
    By lcaseybsa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2013, 08:32 AM
  5. Comparing items in lists
    By Mikeishere in forum Excel General
    Replies: 1
    Last Post: 11-18-2010, 08:17 PM
  6. Comparing items
    By kistonce in forum Excel General
    Replies: 1
    Last Post: 05-03-2010, 12:34 PM
  7. Comparing frequency of items sold together
    By oddcarout in forum Excel General
    Replies: 3
    Last Post: 12-05-2009, 09:47 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1