old top 10 dates

[Danny54]

How would I go about getting a listing of the oldest 5 dates from a column? Using the formula, it returns the date as a number with count


My data in in columns A:F Results from formula in col I

=INDEX(SORTBY(UNIQUE(F1:F9)&" = "&COUNTIFS(F1:F9,UNIQUE(F1:F9)),COUNTIFS(F1:F9,UNIQUE(F1:F9)),-1),SEQUENCE(5))

1​	1​	1​	1​	1​	5/14/2016​			42504 = 2
2​	2​	2​	2​	2​	5/14/2016​			43234 = 1
1​	1​	1​	1​	1​	5/14/2018​			43630 = 1
1​	1​	1​	1​	1​	6/14/2019​			43965 = 1
1​	1​	1​	1​	1​	5/14/2020​			41804 = 1
1​	1​	1​	1​	1​	6/14/2014​

Thanks

 

[Fluff]

What do you want it to return?

 

[Danny54]

What do you want it to return?

6/14/2014 = 1
5/14/2016 = 2
5/14/2018 = 1
6/14/2019 = 1
4/14/2020 = 1

 

[Eric W]

You can try something like:

=INDEX(TEXT(SORT(UNIQUE(F1:F9)),"mm/dd/yyyy"),SEQUENCE(5))&" = "&COUNTIF(F1:F9,INDEX(SORT(UNIQUE(F1:F9)),SEQUENCE(5)))


but this is a great example where using 2 columns for your data is better than trying to combine two values in one. You could use

=INDEX(SORT(UNIQUE(F1:F9)),SEQUENCE(5))

in I1, and format the column as a date, and use

=COUNTIF(F1:F9,I1#)

in J1 and you have 2 shorter, easier formulas.

 

[Fluff]

Two options depending on if you have the new LET function

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	1	1	1	1	1	14/05/2016			14/05/2016 = 2	14/05/2016 = 2
3	2	2	2	2	2	14/05/2016			14/05/2018 = 1	14/05/2018 = 1
4	1	1	1	1	1	14/05/2018			14/06/2019 = 1	14/06/2019 = 1
5	1	1	1	1	1	14/06/2019			14/05/2020 = 1	14/05/2020 = 1
6	1	1	1	1	1	14/05/2020			14/06/2014 = 1	14/06/2014 = 1
7	1	1	1	1	1	14/06/2014
8
9
Master

Cell Formulas
Range	Formula
I2:I6	I2	=INDEX(SORTBY(TEXT(UNIQUE(F1:F9),"dd/mm/yyyy")&" = "&COUNTIFS(F1:F9,UNIQUE(F1:F9)),COUNTIFS(F1:F9,UNIQUE(F1:F9)),-1),SEQUENCE(5))
J2:J6	J2	=LET(Uni,UNIQUE(F1:F9),Cif,COUNTIFS(F1:F9,Uni),INDEX(SORTBY(TEXT(Uni,"dd/mm/yyyy")&" = "&Cif,Cif,-1),SEQUENCE(5)))
Dynamic array formulas.

@Eric W
Unfortunately you're formula does not sort correctly.

 

[Eric W]

@Eric W
Unfortunately you're formula does not sort correctly.

Possible, I'm still using UDFs. But neither of your two formulas match the expected results in Post 3. Why is the oldest date last? Some difference in SORT and SORTBY?

Edit: It appears the expected results are different in post 1 and post 3.

 

[Fluff]

Edit: It appears the expected results are different in post 1 and post 3

Hadn't noticed that.
The OP's formula is sorting by the count, but in post#3 it appears it should be by date.
We'll have to wait & see what the OP says.
If it should be by date then your formula works.

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	1	1	1	1	14/05/2016			14/05/2016 = 2	14/05/2016 = 2	06/14/2014 = 1
3	2	2	2	2	2	14/05/2016			14/05/2018 = 1	14/05/2018 = 1	05/14/2016 = 2
4	1	1	1	1	1	14/05/2018			14/06/2019 = 1	14/06/2019 = 1	05/14/2018 = 1
5	1	1	1	1	1	14/06/2019			14/05/2020 = 1	14/05/2020 = 1	06/14/2019 = 1
6	1	1	1	1	1	14/05/2020			14/06/2014 = 1	14/06/2014 = 1	05/14/2020 = 1
7	1	1	1	1	1	14/06/2014
8
9
Master

Cell Formulas
Range	Formula
I2:I6	I2	=INDEX(SORTBY(TEXT(UNIQUE(F1:F9),"dd/mm/yyyy")&" = "&COUNTIFS(F1:F9,UNIQUE(F1:F9)),COUNTIFS(F1:F9,UNIQUE(F1:F9)),-1),SEQUENCE(5))
J2:J6	J2	=LET(Uni,UNIQUE(F1:F9),Cif,COUNTIFS(F1:F9,Uni),INDEX(SORTBY(TEXT(Uni,"dd/mm/yyyy")&" = "&Cif,Cif,-1),SEQUENCE(5)))
K2:K6	K2	=INDEX(TEXT(SORT(UNIQUE(F1:F9)),"mm/dd/yyyy"),SEQUENCE(5))&" = "&COUNTIF(F1:F9,INDEX(SORT(UNIQUE(F1:F9)),SEQUENCE(5)))
Dynamic array formulas.

 

[Danny54]

sweet, worked like a champ. I appreciate all the help. Messing with dates is confusing sometimes.

Have a great date.

 

[Fluff]

Glad we could help & thanks for the feedback.