How to rank dates in excel on a large group with duplicates, triplicates, and quadruplicates

[rosafranco2020]

Hi all,

I need your expertise! Below is just a sample, but I am working on a larger volume of data containing duplicates, triplicates, and quadruplicates in which I need the newest and second to the newest date records only. I tried using the RANK.EQ function so it gives me 1,2,3,4 etc. and at the end I can filter by 1 & 2 which should be the newest & second newest dates and then copy and paste that into my final report. But the formula I am using (i.e. =RANK.EQ(C2,C$2:C$4) is not working for this large data sheet. I basically need the function to look at the Member SSN since that will drive who/what on the column after "Thru Date" Any ideas that you have will be extremely helpful. Since I am new as of yesterday, I can't get the XL2BB to work. I am so sorry! I am hoping someone can still help me please. Thank you for your time!

Member SSN	Account	Member ID	First Name	Last Name	DOB	From Date	Thru Date
123434697	Active	0123	Paris	France	01/01/1991	02/01/2019	02/28/2019
123434697	Active	0123	Paris	France	01/01/1991	03/01/2019	08/01/2019
123434697	Active	0123	Paris	France	01/01/1991	08/01/2019	12/31/2039
478908765	Active	0987	Bud	Light	03/23/1965	11/01/2020	12/31/2039
478908765	Active	0987	Bud	Light	03/23/1965	06/01/2019	10/30/2020
908239089	Active	8765	Corona	Virisu	07/23/1956	09/01/2020	09/30/2020
908239089	Active	8765	Corona	Virisu	07/23/1956	10/01/2020	12/31/2039
089092345	Active	3454	Zen	Vu	4/15/1980	10/01/2020	12/31/2039
089092345	Active	3454	Zen	Vu	4/15/1980	01/01/2019	09/30/2020

 

[rosafranco2020]

@Fluff you rock!

 

[airbisglenn]

using =RANK(BM5,BM$5:BM$999,1) to get rank but need to a birthday month and day to break ties?

tring to add countif but no luck can anyone help?

 

[Fluff]

Please start a thread of your own for this question. Thanks