0

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?

  • can you share copy of your sheet? – user0 Jun 13 '18 at 12:12
  • @user902300 The actual sheet is a massive mess full of several other things going on, I tried to boil this question down to only the relevant details. That was why I provided a little mini example sheet. Is it usual protocol here to link a sheet to be tested with? I'm new to this stackexchange, but can do so in the future :) – Emmabee Jun 13 '18 at 14:08
  • not sure about "usual protocol", but it would speed up your answer if you would provide either copy or dummy sheet to play with. – user0 Jun 13 '18 at 14:18
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.