Forum Discussion
How to identify if Rows that have a matching value - does a specific column also have matching data?
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
| Number | Reason |
| 1 | expired |
| 1 | not expired |
| 1 | expired |
| 1 | expired |
| 3 | good |
| 3 | bad |
| 3 | bad |
| 2 | forget |
| 2 | forget |
| 4 | expired |
| 4 | expired |
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.
1 Reply
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.