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

1. 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. 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)))

3. 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. 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))))

User Tag List

Posting Permissions

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