1

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,

Example

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?

2 Answers 2

0

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

-1

i think your problem is simple

just make new format like this my tabble 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

conditional formatting

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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