Highlight duplicate values between two or more ranges

2
votes

I have a range of cells that I want to highlight duplicates in the same column. using =match(C$21,C$7:C$10,0) applied to the range C7:H10.

Expected outcome:

  1. If there is a duplicate in the range it highlights the whole range of that column ie C7:C10, I would like it to highlight only the duplicated cell. ie C7.

  2. I need it to look for conflicts between the range C7:C10 and C21:C24. on my test sheet shared below I have colored the rows that should not duplicate with light purple, or light magenta (two seperate conflict groups). Columns C, D, E have the conditional formatting. Columns G, H are static formatted the way I would like it to show.

Any help appreciated.

Highlight Test Sheet

1
regexgoogle-sheetsgoogle-sheets-formulags-conditional-formattingtextjoin

1 Answers

1
votes

orange group:

=REGEXMATCH(C7, TEXTJOIN("|", 1, $C$21:$H$24))

blue group:

=REGEXMATCH(C11, TEXTJOIN("|", 1, $C$23:$H$25))

0

spreadsheet demo