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

 

[Ezguy4u]

Let's get the ball rolling. Now I am going to go the VBA route first. I am going to see if I can figure a formula or function next. This should generate a discussion and lots of questions. But we have to start some place. Look this over and let us know if it's what you were thinking.

Option Explicit

Sub DateSort1()

Dim firstrow As Long
Dim lastrow As Long
Dim myrange As Range
Dim outrow As Long

firstrow = 2
outrow = 2
Cells(1, 10) = "SSN"
Cells(1, 11) = "1st Date"
Cells(1, 12) = "2nd Date"


Do Until Cells(firstrow, 1) = ""
lastrow = firstrow
    Do Until Cells(lastrow, 1) <> Cells(lastrow + 1, 1)
    lastrow = lastrow + 1
    Loop
Set myrange = Range(Cells(firstrow, 8), Cells(lastrow, 8))

Cells(outrow, 10) = Cells(lastrow, 1)
Cells(outrow, 11) = Application.WorksheetFunction.Large(myrange, 1)
Cells(outrow, 12) = Application.WorksheetFunction.Large(myrange, 2)

firstrow = lastrow + 1
outrow = outrow + 1
Loop

End Sub

20-10-10 sort 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Member SSN	Account	Member ID	First Name	Last Name	DOB	From Date	Thru Date		SSN	1st Date	2nd Date
2	123434697	Active	123	Paris	France	1/1/1991	2/1/2019	2/28/2019		123434697	12/31/19	10/20/19
3	123434697	Active	123	Paris	France	1/1/1991	3/1/2019	8/1/2019		478908765	12/15/20	11/12/20
4	123434697	Active	123	Paris	France	1/1/1991	8/1/2019	12/31/2019		908239089	12/31/20	11/25/20
5	123434697	Active	123	Paris	France	1/1/1991	2/1/2019	2/28/2019		89092345	12/31/20	9/30/20
6	123434697	Active	123	Paris	France	1/1/1991	3/1/2019	10/1/2019
7	123434697	Active	123	Paris	France	1/1/1991	8/1/2019	10/20/2019
8	478908765	Active	987	Bud	Light	3/23/1965	11/1/2020	12/15/2020
9	478908765	Active	987	Bud	Light	3/23/1965	6/1/2019	10/30/2020
10	478908765	Active	987	Bud	Light	3/23/1965	5/1/2020	11/12/2020
11	478908765	Active	987	Bud	Light	3/23/1965	6/1/2019	7/30/2020
12	908239089	Active	8765	Corona	Virisu	7/23/1956	9/1/2020	9/30/2020
13	908239089	Active	8765	Corona	Virisu	7/23/1956	10/1/2020	11/25/2020
14	908239089	Active	8765	Corona	Virisu	7/23/1956	9/1/2020	10/15/2020
15	908239089	Active	8765	Corona	Virisu	7/23/1956	10/1/2020	12/31/2020
16	89092345	Active	3454	Zen	Vu	4/15/1980	10/1/2020	12/31/2020
17	89092345	Active	3454	Zen	Vu	4/15/1980	1/1/2019	9/30/2020
Date Sort

 

[DRSteele]

Are you trying to rank things with conditions? This article deals with that.

 

[rosafranco2020]

I was really wanting a formula, I’m not sure how to use VBA. It’s awesome work though because the conditions are providing the results I need @Ezguy4u but do you have a function/formula you recommend?

 

[Ezguy4u]

Ok so the good news is you approve of the input and the output, based on the above example. Now that means that other excel experts will be looking at this and maybe they can also come up with a function\formula solution. So let's give this some time and see what happens. I am going to see if I can come up with a function\formula, but one of the biggest problems is separating the SSN using a function\formula.

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			Member SSN	Account	Member ID	First Name	Last Name	DOB	From Date	Thru Date
2			123434697	Active	123	Paris	France	01/01/1991	01/02/2019	28/02/2019	6
3			123434697	Active	123	Paris	France	01/01/1991	01/03/2019	01/08/2019	5
4			123434697	Active	123	Paris	France	01/01/1991	01/08/2019	01/12/2019	4
5			123434697	Active	123	Paris	France	01/01/1991	01/08/2019	10/12/2019	3
6			123434697	Active	123	Paris	France	01/01/1991	01/08/2019	31/12/2019	1
7			123434697	Active	123	Paris	France	01/01/1991	01/08/2019	31/12/2019	1
8			478908765	Active	987	Bud	Light	23/03/1965	01/11/2020	31/12/2019	3
9			478908765	Active	987	Bud	Light	23/03/1965	01/11/2020	10/10/2019	4
10			478908765	Active	987	Bud	Light	23/03/1965	01/11/2020	01/05/2020	2
11			478908765	Active	987	Bud	Light	23/03/1965	01/11/2020	01/08/2020	1
12			908239089	Active	8765	Corona	Virisu	23/07/1956	01/09/2020	30/09/2020	2
13			908239089	Active	8765	Corona	Virisu	23/07/1956	01/10/2020	31/12/2039	1
14			89092345	Active	3454	Zen	Vu	15/04/1980	01/10/2020	31/12/2039	1
15			89092345	Active	3454	Zen	Vu	15/04/1980	01/01/2019	30/09/2020	2
Data

Cell Formulas
Range	Formula
K2:K15	K2	=COUNTIFS(C:C,C2,J:J,">"&J2)+1

 

[rosafranco2020]

@Ezguy4u your example is exactly what I need! So I will be patient to see if other excel experts can formulate it.
@Fluff this is good! I believe I can use this, can we also do it for column I? Because both dates From/Thru are important.
Thank you guys! Your skills are extraordinary

 

[Fluff]

Can you post some sample data along with the expected results.

 

[rosafranco2020]

@Fluff I think I figured it out! I added to the countifs and using the =SUMPRODUCT((($C$2:$C$15=C2)*($J$2:$J$15<J2))/(COUNTIFS($J$2:$J$2:$J$15,$C$2:$C$15,C2)+($C$2:$C$15<>C2)))+1

I wish I could run this in a Macro, lol. Thanks guys

 

[Fluff]

Glad you sorted it & thanks for the feedback.