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

Question

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?

Answer 1

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

Answer 2

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