SOLVED

How to identify if Rows that have a matching value - does a specific column also have matching data?

Copper Contributor

I need a formula that will tell me for each row that has a value of "1" in column A - do the values in column B match? (i.e. For all numbers that say "1" column B should say expired for all of those, however in this example there is 1 line item that does not match, it says "not expired". Technically, they should all match but they do not - I need to easily know the line items that have ones that don't match. Number 4 in column A is perfect because column b matches for both those line items. 

Example data 

NumberReason
1expired
1not expired
1expired
1expired
3good
3bad
3bad
2forget
2forget
4expired
4expired

 

 

1 Reply
best response confirmed by Grimes65 (Copper Contributor)
Solution

@Grimes65 

Enter the following formula in C2, then fill down:

 

=COUNTA(UNIQUE(FILTER($B$2:$B$10000, $A$2:$A$10000=A2)))>1

 

This will return TRUE for the rows of numbers with more than one unique problem.

You can sort or filter on column C if desired.

HansVogelaar_0-1696619575308.png

1 best response

Accepted Solutions
best response confirmed by Grimes65 (Copper Contributor)
Solution

@Grimes65 

Enter the following formula in C2, then fill down:

 

=COUNTA(UNIQUE(FILTER($B$2:$B$10000, $A$2:$A$10000=A2)))>1

 

This will return TRUE for the rows of numbers with more than one unique problem.

You can sort or filter on column C if desired.

HansVogelaar_0-1696619575308.png

View solution in original post