CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
RedXian
RedXian

Reputation: 53

Highlighting duplicates over multiple rows using conditional formatting

I'm trying to create a Google spreadsheet to organise a seating plan. I've laid out the page with 11 separated smaller tables in a kind of grid format (for easier reading, as you can see it all without scrolling). Like this: 

      Table 1       |      Table 2       |      Table 3       |
 Seat | Name | Diet | Seat | Name | Diet | Seat | Name | Diet |
    1 |      |      |    1 |      |      |    1 |      |      |
    2 |      |      |    2 |      |      |    2 |      |      |

I'm trying to create a conditional format to highlight cells where a name appears more than once.

So I created a conditional format with a custom formula as follows (the range covers the all tables):

COUNTIF($A$1:$O$42, A1)>1;

and I set the range to only the Name cells on the page.

However when I purposely set a duplicate the cells are not highlighted.

I thought that maybe my formula was wrong, so I put into a cell and pointed it at a duplicate cell and it returned TRUE.

So I'm at a loss a how to get it working.

Upvotes: 2

Views: 5043

Answers (1)

Chris Hick
Chris Hick

Reputation: 3094

Try this formula applied to range A3:O

=AND(ISTEXT(A3),COUNTIF($A$3:$O$42, A3)>1)

Example sheet is here: https://goo.gl/hChZbt

Upvotes: 1

Related Questions