# Highlight duplicates from four or more separate range in the same sheet

I want to highlight duplicate value in four separate range for a lessons schedule using conditional formatting in google sheets, in this example there are four classes,

what i'm trying to achieve are :

1. in lesson 1 class A, B, C & D all have different subject so they won't be highlighted
2. in lesson 2 class A and class D have the same subject, so the same subject "Ipa" will be higlighted in both class A and class D
3. in lesson 3 class A, B and C have the same subject so the same subject "Bing" will be highlighted in class A, B and C

is anyone know the formula for this? or is it possible the achieve this if they're four separate cells rather than four separate range?

Both approaches below count how many times the current string appears in an array of strings. Since all the strings appear in pairs, any count greater than 2 means the string is duplicated.

If you want to add more classes to the same comparison you would change the ranges in the formula to include the additional classes.

Offsetting the data you are comparing complicates things for you, so you may consider rearranging if practical.

# Row Pairs Lined up

A simpler solution because the data being compared is in the same row pairs.

A B C D
1 Class A Class B Class C Class D
2 Bindo SKI TIK SBDP
3 Bindo SKI TIK SBDP
4
5 Ipa PAI PJOK Ipa
6 Ipa PAI PJOK Ipa
7
8 Bing Bing Bing Mtk
9 Bing Bing Bing Mtk

### Rules

The following three conditional formatting rules are required:

``````# Apply to Range
A2:D3

# Custom Formula:
=COUNTIF(\$A\$2:\$D\$3,A2)>2
``````
``````# Apply to Range
A5:D6

# Custom Formula:
=COUNTIF(\$A\$5:\$D\$6,A5)>2
``````
``````# Apply to Range
A8:D9

# Custom Formula:
=COUNTIF(\$A\$8:\$D\$9,A8)>2
``````

Formatting

Rule

# Row Pairs Offset

A more complicated solution where the row pairs are offset:

A B C
1 Class A Class B
2 Bindo SKI
3 Bindo SKI
4
5 Ipa PAI
6 Ipa PAI
7
8 Bing Bing
9 Bing Bing
10
11 Class C Class D
12 TIK SBDP
13 TIK SBDP
14
15 PJOK Ipa
16 PJOK Ipa
17
18 Bing Mtk
19 Bing Mtk

### Rules

The following 3 conditional formatting rules are required:

``````# Apply to Range
A2:C3,A12:C13

# Custom Formula:
=COUNTIF(\$A\$2:\$C\$3,\$A\$12:\$C\$13,A2)>2
``````
``````# Apply to Range
A5:C6,A15:C16

# Custom Formula:
=COUNTIF({\$A\$5:\$C\$6,\$A\$15:\$C\$16},A5)>2
``````
``````# Apply to Range
A8:C9,A18:C19

# Custom Formula:
=COUNTIF({\$A\$8:\$C\$9,\$A\$18:\$C\$19},A8)>2
``````

Formatting

Rule

i think your problem is simple

just make new format like this so you can add more class in the right, and just one formula for the lesson list in my example

``````#apply to lesson range
B2:E7

conditional formatting
=countif(\$b2:\$e2;b2)>1
``````

explanation : it count from column B to E, and use \$ to lock the column so it wont give the wrong result, and row that countains more than 1 same lesson, the cell will be highlighted