Highlight one of duplicates base on values in another column

[ShyamExcel]

This should be an easy one but my brain is sluggish today, so any help is appreciated.


I have 2 columns, one with names and other with age. the name column could have duplicates. If a name has a duplicate, I want to highlight oldest person. Is there are conditional formatting formula that we can write that would do this? or any other formula?


Here's a sample.

Name Age

Sam 15

Sheela 23

Sam 11

Hui 16

Chandoo 24

Chandoo 25

Sam 12

Narayan 26

Faseeh 27

Narayan 18


So I would like the list to only retain the unique names by deleting all but the oldest.


Name Age

Sam 15

Sheela 23

Hui 16

Chandoo 25

Narayan 26

Faseeh 27


So here you see that Sam 12, Sam 11 Chandoo 24 and Narayan 18 are removed since they are the younger of the duplicate (could be more than one version as with Sam) names.


Thanks

shyam

 

[NARAYANK991]

Hi Shyam ,


Try this , as the CF rule :


=$G8=INDEX($G$8:$G$17,MATCH(MAX(IF($F$8:$F$17=F8,$G$8:$G$17)),IF($F$8:$F$17=F8,$G$8:$G$17),0))


I have assumed the following :


1. Your list of names is in the range F8:F17


2. Your list of ages is in the range G8:G17


Select your range of ages , and enter the above formula in the CF box , and select any Fill color of your choice.


If you wish to create a separate list , which has only the names and oldest ages of the people , then use the following two formulae , entered as array formulae , using CTRL SHIFT ENTER :


1. =INDEX(Names,MATCH(0,COUNTIF($J$7:J7,Names),0))


This will list out the names removing duplicate occurrences. I have assumed that your new list starts from J8 , and J7 is either blank , or does not contain a name from the list of names.


2. =INDEX(Ages,MATCH(MAX(IF(Names=J8,Ages)),IF(Names=J8,Ages),0))


This will enter the oldest ages of the people whose names are listed through formula 1 above.


Narayan

 

[Faseeh]

ShyamExcel,


Try this also:


With you data in A2:A11, set "conditional formatting rule" to following formula:


=COUNTIF($A$2:A2,A2)=1


Regards,


Faseeh

 

[Santy28]

Sort the data Selecting the range (Column A: Names & Column B: Age) with Names (A to Z) and Age with (Longest to Small).Once u sorted the data type Check in column c and in C2 type(=A2=A1) paste the same formula till end of data in column B. Apply filter and select False. There you go all the False values are the unique ones with Oldest Ages.

 

[ShyamExcel]

Thanks guys. Appreciate it.