how to find if any unique values in two columns for each value in one column
Results 1 to 4 of 4

Thread: how to find if any unique values in two columns for each value in one column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how to find if any unique values in two columns for each value in one column

    I have two columns. column A has text, column B has a numeric ID.

    A B
    Pizza 40
    Candy 20
    Soda 30
    Broccoli 20
    Pizza 40

    Every time "40" appears in column B, I want to make sure that all the respective values for 40 in column A, are the same. In this case, they're all "Pizza".

    I want to know if there are ever any multiple unique values. In this case, "20" has two unique values - "Candy" and "Broccoli".


    How can I do this without having a formula that I would need to drag down all throughout a new column.

    Ideally, I'd like to be able to have just 4 cells for 4 formulas (one for each unique numeric code). is this possible?

    hope this was clear

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: how to find if any unique values in two columns for each value in one column

    Try:

    A B C D E
    1 Food Value Values All the same?
    2 Pizza 40 10 TRUE
    3 Candy 20 20 FALSE
    4 Soda 30 30 TRUE
    5 Broccoli 20 40 TRUE
    6 Pizza 40
    Sheet6

    Worksheet Formulas
    Cell Formula
    E2 =COUNTIF($B$2:$B$6,D2)=COUNTIFS($B$2:$B$6,D2,$A$2:$A$6,INDEX($A$2:$A$6,MATCH(D2,$B$2:$B$6,0)))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Mar 2006
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to find if any unique values in two columns for each value in one column

    perfect. thanks Eric.

    In the case of column D, value "10". Since this doesn't exist in column B, is there a way I can have it return text other than "true"

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: how to find if any unique values in two columns for each value in one column

    Try:

    =IF(COUNTIF($B$2:$B$6,D2)=0,"None found",COUNTIF($B$2:$B$6,D2)=COUNTIFS($B$2:$B$6,D2,$A$2:$A$6,INDEX($A$2:$A$6,MATCH(D2,$B$2:$B$6,0))))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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