Excel Formula – Match Duplicate and Highlight with different color

Posted on

QUESTION :

enter image description here

A to D column with title List-1,List-2,List-3,List-4 and “Main List” A12 to A106 (original “Main List” DATA I did cut/paste here for color showing purpose)

There are random name showing in A2 to D10 which have 3 or 4 times duplicate match within A2:D10

I want to Highlight Duplicate match with different color like
if same name match 4 times then it should be HIGHLIGHT with GREEN background,

if 3 times match then should be highlight with BLUE background and

if 2 times match highlight with Yellow background.

SAMPLE image attached.

Please help me with excel formula or guide me if any possible way.

ANSWER :

Its done with below steps.

Select A2 to D10 -> Conditional Formula -> use below formula (one by one) -> Color

=COUNTIF($A$2:$D$10,A2)=4 (Blue)

=COUNTIF($A$2:$D$10,A2)=3 (Yellow)

=COUNTIF($A$2:$D$10,A2)=2 (Grey)

Leave a Reply

Your email address will not be published.