Returning oldest date and associated cell info from a range dependent on contents of another cell

[rrands1]

For the following data:

I am trying to populate a cell with the oldest "Date Out" (Column J) that does not have a corresponding "Date In" (Column K). The intent is to figure out the oldest date out that has not been returned (Date In), and show the "Assigned to" name as a result. I have the following, which works to find the oldest "Date Out" and show the contents of that row's "Assigned To" cell, however I can't figure out how to only check on ones that don't have anything in the "Date In" cell. Here is my formula so far:
=INDEX(I2:I5,MATCH(MIN(K2:K5),K2:K5,0))

So this would return 7/4/2021, however that has a value in column K (meaning it has been returned), so the value I really am after is 8/28/21 because it's the oldest date that has not been returned. Does anyone know how to make that work?


BONUS Q: This finds the oldest date, but I ideally would like the 3 oldest - is there a way to modify the MIN function (or use a different one) to do like MIN -1, MIN -2?


Thank you so much - I appreciate any help!

 

[alansidman]

@Peter_SSs

To test my explanation I added a few more records to your sample then built a parameter query into the Mcode I earlier provided.

Book9
	A	B	C	D	E	F	G
1	Assigned To	Date Out	Date in			Oldest how many:
2	Name 1	4/17/2021	7/4/2021			3
3	Name 2	4/13/2021	4/25/2021
4	Name 3	1/3/2021				Assigned To	Date Out
5	Name 4	7/28/2021	9/20/2021			Name 3	1/3/2021
6	Name 5	5/11/2021				Name 8	2/5/2021
7	Name 6	10/12/2021	10/12/2021			Name10	3/1/2021
8	Name 7	6/24/2021
9	Name 8	2/5/2021
10	Name 9	8/31/2021	10/10/2021
11	Name10	3/1/2021
12	Name11	3/15/2021	3/17/2021
Sheet1

The code for the parameter query called "Oldest" is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Oldest how many:" = Source{0}[#"Oldest how many:"]
in
    #"Oldest how many:"

and the code where the parameter is now placed into the original query is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Out", type date}, {"Date in", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date in] = null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date Out", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] < Oldest +1)), 
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Date in", "Index"})
in
    #"Removed Columns"

 

[rrands1]

If you do not have 365, or just need it to work in older versions as well, try these

21 10 20.xlsm
	I	J	K	L	M	N	O
1	Assigned To	Date Out	Date in			Oldest how many:	3
2	Name 1	17/04/2021	4/07/2021
3	Name 2	13/04/2021	25/04/2021			Assigned To	Date Out
4	Name 3	3/01/2021				Name 3	3/01/2021
5	Name 4	28/07/2021	20/09/2021			Name 8	5/02/2021
6	Name 5	11/05/2021				Name 5	11/05/2021
7	Name 6	12/10/2021	12/10/2021
8	Name 7	24/06/2021
9	Name 8	5/02/2021
10	Name 9	31/08/2021	10/10/2021
Oldest Out (2)

Cell Formulas
Range	Formula
N4:N10	N4	=IF(O4="","",INDEX(I:I,AGGREGATE(15,6,ROW(I$2:I$10)/((J$2:J$10=O4)*(K$2:K$10="")),COUNTIF(O$4:O4,O4))))
O4:O10	O4	=IF(ROWS(O$4:O4)>O$1,"",AGGREGATE(15,6,J$2:J$10/(K$2:K$10=0),ROWS(O$4:O4)))

@Peter_SSs (and all who replied) - thank you *so much* for your help on this - I really appreciate it! Peter's code here is the closest to what I need (I am on o365, and have updated my profile accordingly). I am running into a couple of things, and wondered if there is a way to account for it?


1. I have some blank rows in between the sections of data, and that is causing the formula to not evaluate anything after the first blank row. Is it possible to have this ignore those blank rows?

2. I also have some cells in column J (Date Out) that are empty, and this also causes an issue (Date Out in column O shows 1/0/1900) - it is possible to have the formula ignore those blank cells?


Thank you again - I appreciate it!

 

[Peter_SSs]

Thanks for updating your profile.

I'm confused by the fact that you have 365 but you have quoted my post that was for other versions as well, and apparently seem to be using those formulas, not the 365 formulas.
From what I can understand of your description, the 365 formulas work as expected. Is there something wrong with my sample data or results?

rrands.xlsm
	I	J	K	L	M	N	O
1	Assigned To	Date Out	Date in			Oldest how many:	3
2	Name 1	17/04/2021	4/07/2021
3	Name 2	13/04/2021	25/04/2021			Assigned To	Date Out
4	Name 3	3/01/2021				Name 3	3/01/2021
5	Name 4	5/04/2021				Name 11	5/02/2021
6						Name 4	5/04/2021
7	Name 6	28/07/2021	20/09/2021
8	Name 7
9	Name 8	12/10/2021	12/10/2021
10
11
12	Name 9
13	Name 10	24/06/2021
14	Name 11	5/02/2021
15	Name 12	31/08/2021	10/10/2021
Oldest Out

Cell Formulas
Range	Formula
N4:O6	N4	=INDEX(SORTBY(FILTER(I2:J15,K2:K15=""),FILTER(J2:J15,K2:K15="")),SEQUENCE(O1),{1,2})
Dynamic array formulas.

 

[rrands1]

Thanks for updating your profile.

I'm confused by the fact that you have 365 but you have quoted my post that was for other versions as well, and apparently seem to be using those formulas, not the 365 formulas.
From what I can understand of your description, the 365 formulas work as expected. Is there something wrong with my sample data or results?

rrands.xlsm
	I	J	K	L	M	N	O
1	Assigned To	Date Out	Date in			Oldest how many:	3
2	Name 1	17/04/2021	4/07/2021
3	Name 2	13/04/2021	25/04/2021			Assigned To	Date Out
4	Name 3	3/01/2021				Name 3	3/01/2021
5	Name 4	5/04/2021				Name 11	5/02/2021
6						Name 4	5/04/2021
7	Name 6	28/07/2021	20/09/2021
8	Name 7
9	Name 8	12/10/2021	12/10/2021
10
11
12	Name 9
13	Name 10	24/06/2021
14	Name 11	5/02/2021
15	Name 12	31/08/2021	10/10/2021
Oldest Out

Cell Formulas
Range	Formula
N4:O6	N4	=INDEX(SORTBY(FILTER(I2:J15,K2:K15=""),FILTER(J2:J15,K2:K15="")),SEQUENCE(O1),{1,2})
Dynamic array formulas.

This worked - THANK YOU, THANK YOU, THANK YOU!!

I appreciate it very much - I learned a ton just from this post - some functions I was not aware of before, so thanks for that, too!

-randy

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.