SOLVED

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

Copper Contributor

# 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

best response confirmed by Grimes65 (Copper Contributor)
Solution

# Re: How to identify if Rows that have a matching value - does a specific column also have matching d

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 best response

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

# Re: How to identify if Rows that have a matching value - does a specific column also have matching d

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.