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

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,289
Try:

ABCDE
1FoodValueValuesAll the same?
2Pizza4010TRUE
3Candy2020FALSE
4Soda3030TRUE
5Broccoli2040TRUE
6Pizza40

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
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)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
158
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,289
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))))
 

Forum statistics

Threads
1,089,192
Messages
5,406,729
Members
403,104
Latest member
mkirchner

This Week's Hot Topics

Top