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

[banneduser123]

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

 

[Eric W]

Try:

	A	B	C	D	E
Food	Value	Values	All the same?
Pizza
Candy
Soda
Broccoli
Pizza

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[banneduser123]

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"

 

[Eric W]

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