2

In Column C, I Want to rename the Column A Name in order of Date Of Birth, but only if a duplicate is found. Otherwise, the name in Column C should be the same as Column A. For example, there are two Ajays, so the elder is Ajay 1, the younger is Ajay 2. 

     [A]        [B]           [C]                   
    Name    Date of Birth   New Name            
    Ajay    18/05/2009      Ajay 2          
    Rajesh  12/04/2003      Rajesh 2        
    Sunil   13/02/1990      Sunil           
    Rajesh  13/04/1999      Rajesh 1            
    Ajay    12/01/2000      Ajay 1  
    Rajesh  12/04/2008      Rajesh 3

I tried this, which did not work: 

    IF(COUNTIFS($A$2:$A$7,A2)>1,MATCH(A2,$A$2:$A$7,0),0)

One friend suggested: 

    =$A1&IF(COUNTIF($A:$A,$A1)>1," "&COUNTIF($A$1:$A1,$A1),"")

but it does not rename in decreasing order of age

fixer1234
  • 28,064
Ajay Badgujar
  • 29

1 Answers1

0

Try in cell C2 and copy down

=IF(COUNTIF($A$2:$A$7,A2)>1,A2&" "&SUMPRODUCT(--($A$2:$A$7=A2),--(B2>$B$2:$B$7))+1,A2)

enter image description here

Adjust formula to your range. Don't use whole column ranges like A:A or B:B inside the Sumproduct function. Use the real start and end row instead, like $A$2:$A$500 -- otherwise the calculation may take a long time.

teylyn
  • 23,615