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.


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)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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