Google-sheets – Conditional formatting based on dependent variable cells

conditional formattinggoogle sheets

Apologies if the title is unclear — if anyone can suggest better wording I will change it. I wasn't sure how to condense this question into a single sentence.

I have a spreadsheet with three columns and I need it to be sortable by all three, however, some of the values in column A have multiple values tied to them in column C, and therefore have multiple entries, but the different entries cannot have different values tied to them in column B.

I need the row to indicate an error (in the form of a different color) if whatever value is in column A appears elsewhere with a different associated value in column B.

For example:

Mike      7       Green
Mike      7       Blue
Patrick   7       Green
Sarah     12      Purple
Sarah     11      Green

In this list I would like both of Sarah's rows to be highlighted in red, regardless of whether or not they are currently next to each other(I can make it work if they're next to each other).

I don't know of a way of actually locating a duplicate cell, only checking if one exists with COUNTIF(), so I'm not sure how to then reference the cell next to it.

Is this possible?

Best Answer

I slept on it, and the solution miraculously came to me! Here's how I've gotten it working, in case anyone else is attempting something similar.

First off I am counting the number of times a value appears in Column A (the names column, in my example).

COUNTIF($A$1:$B,$A1)

This will return a value of 1 for Patrick and a value of 2 for both Mike and Sarah.

I am using a FILTER() function to create a searchable range which only includes rows which have the same Column A value as the row I'm currently formatting.

FILTER($A$1:$B,EQ($A$1:$A,$A1))

I am then using this as the range for a second COUNTIF() function, which will count how many times the value in Column B appears.

COUNTIF(FILTER($A$1:$B,EQ($A$1:$A,$A1)),$B1)

This will return a 1 for Patrick and a 2 for both of Mike's rows, the same as the value from the previous COUNTIF(). However, both of Sarah's rows will return a 1, since "12" and "11" both appear only once beside her name.

If all appearances of the Column A value have the same Column B value, then these numbers should match. If there are multiple unique Column B values, the second number will be smaller. So all that remains is to compare the two and return TRUE if my first number is greater than the second.

Full formula:

=COUNTIF($A$1:$B,$A1)>COUNTIF(FILTER($A$1:$B,EQ($A$1:$A,$A1)),$B1)

This will return FALSE for Patrick and Mike, but TRUE for Sarah's rows, correctly flagging them as containing an error.

Related Topic