Need to pull a min or max date from multiple criteria

[xxrichbxx]

Hello, currently pulling my hair out !

I am wanting to pull the earliest start date and the latest completion date into a report which should hopefully give a span of the activities.

I am currently using a vlookup/index match to pull the the data together which is great if there is just one entry for each line, but when there are multiples it becomes a bit of a problem.

I have attached an extract from my spreadsheet.

Columns O - S is my data set and Columns A - G are where I would like to see my extracted data.

Not that the Crane/MEWP min & Max needs to return into columns D&E and the Access Permits return in Columns F&G. The Raw data is held in Columns R&S

Thanks for looking and hopefully this is pretty straight forward.

Min MAx.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1				Crane/MEWP		Access_Permits
2	Unique Ref (Crane/MEWP)	Unique Ref (Access Permit)	Client Ref	Start Scheduled Date	Scheduled Completion Date	Start Scheduled Date	Scheduled Completion Date								Unique Ref	Activity	Client Ref	Start Scheduled Date	Scheduled Completion Date
3	237924Crane_MEWP	237924Access_Permits	237924												237924Crane_MEWP	303706_237924_1_Crane/MEWP_3	237924	16/07/2023	23/07/2023
4	238013Crane_MEWP	238013Access_Permits	238013												237924Crane_MEWP	303706_237924_1_Crane/MEWP_1	237924
5															237924Crane_MEWP	303706_237924_1_Crane/MEWP_2	237924	12/02/2023	19/02/2023
6															237924Access_Permits	303706_237924_1_Access Permits_2	237924	16/07/2023	23/07/2023
7															237924Access_Permits	303706_237924_1_Access Permits_1	237924	12/02/2023	16/02/2023
8															238013Access_Permits	96152_238013_1_Access Permits_1	238013	27/04/2022	04/05/2022
9															238013Access_Permits	96152_238013_1_Access Permits_2	238013	12/04/2022	12/04/2022
10															238013Access_Permits	96152_238013_1_Access Permits_3	238013	03/05/2022	04/05/2022
11															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	29/04/2022	27/04/2022
12															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	28/04/2022	27/04/2022
13
14
Sheet1

 

[KRice]

try this:

Book3
	A	B	C	D	E	F	G								O	P	Q	R	S
1				Crane/MEWP		Access_Permits
2	Unique Ref (Crane/MEWP)	Unique Ref (Access Permit)	Client Ref	Start Scheduled Date	Scheduled Completion Date	Start Scheduled Date	Scheduled Completion Date								Unique Ref	Activity	Client Ref	Start Scheduled Date	Scheduled Completion Date
3	237924Crane_MEWP	237924Access_Permits	237924	2/12/2023	7/23/2023	2/12/2023	7/23/2023								237924Crane_MEWP	303706_237924_1_Crane/MEWP_3	237924	7/16/2023	7/23/2023
4	238013Crane_MEWP	238013Access_Permits	238013	4/28/2022	4/27/2022	4/12/2022	5/4/2022								237924Crane_MEWP	303706_237924_1_Crane/MEWP_1	237924
5															237924Crane_MEWP	303706_237924_1_Crane/MEWP_2	237924	2/12/2023	2/19/2023
6															237924Access_Permits	303706_237924_1_Access Permits_2	237924	7/16/2023	7/23/2023
7															237924Access_Permits	303706_237924_1_Access Permits_1	237924	2/12/2023	2/16/2023
8															238013Access_Permits	96152_238013_1_Access Permits_1	238013	4/27/2022	5/4/2022
9															238013Access_Permits	96152_238013_1_Access Permits_2	238013	4/12/2022	4/12/2022
10															238013Access_Permits	96152_238013_1_Access Permits_3	238013	5/3/2022	5/4/2022
11															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	4/29/2022	4/27/2022
12															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	4/28/2022	4/27/2022
Sheet1

Cell Formulas
Range	Formula
D3:D4	D3	=MIN(FILTER($R$3:$R$12,($O$3:$O$12=$A3)*($R$3:$R$12<>"")))
E3:E4	E3	=MAX(FILTER($S$3:$S$12,($O$3:$O$12=$A3)*($S$3:$S$12<>"")))
F3:F4	F3	=MIN(FILTER($R$3:$R$12,($O$3:$O$12=$B3)*($R$3:$R$12<>"")))
G3:G4	G3	=MAX(FILTER($S$3:$S$12,($O$3:$O$12=$B3)*($S$3:$S$12<>"")))

 

[xxrichbxx]

try this:

Book3
	A	B	C	D	E	F	G								O	P	Q	R	S
1				Crane/MEWP		Access_Permits
2	Unique Ref (Crane/MEWP)	Unique Ref (Access Permit)	Client Ref	Start Scheduled Date	Scheduled Completion Date	Start Scheduled Date	Scheduled Completion Date								Unique Ref	Activity	Client Ref	Start Scheduled Date	Scheduled Completion Date
3	237924Crane_MEWP	237924Access_Permits	237924	2/12/2023	7/23/2023	2/12/2023	7/23/2023								237924Crane_MEWP	303706_237924_1_Crane/MEWP_3	237924	7/16/2023	7/23/2023
4	238013Crane_MEWP	238013Access_Permits	238013	4/28/2022	4/27/2022	4/12/2022	5/4/2022								237924Crane_MEWP	303706_237924_1_Crane/MEWP_1	237924
5															237924Crane_MEWP	303706_237924_1_Crane/MEWP_2	237924	2/12/2023	2/19/2023
6															237924Access_Permits	303706_237924_1_Access Permits_2	237924	7/16/2023	7/23/2023
7															237924Access_Permits	303706_237924_1_Access Permits_1	237924	2/12/2023	2/16/2023
8															238013Access_Permits	96152_238013_1_Access Permits_1	238013	4/27/2022	5/4/2022
9															238013Access_Permits	96152_238013_1_Access Permits_2	238013	4/12/2022	4/12/2022
10															238013Access_Permits	96152_238013_1_Access Permits_3	238013	5/3/2022	5/4/2022
11															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	4/29/2022	4/27/2022
12															238013Crane_MEWP	96152_238013_1_Crane/MEWP_1	238013	4/28/2022	4/27/2022
Sheet1

Cell Formulas
Range	Formula
D3:D4	D3	=MIN(FILTER($R$3:$R$12,($O$3:$O$12=$A3)*($R$3:$R$12<>"")))
E3:E4	E3	=MAX(FILTER($S$3:$S$12,($O$3:$O$12=$A3)*($S$3:$S$12<>"")))
F3:F4	F3	=MIN(FILTER($R$3:$R$12,($O$3:$O$12=$B3)*($R$3:$R$12<>"")))
G3:G4	G3	=MAX(FILTER($S$3:$S$12,($O$3:$O$12=$B3)*($S$3:$S$12<>"")))

Thank you for your response, it's really appreciated.

I added the formula and it came back with #CALC! if I click on the exclamation it tells me that the array is empty.

The below is in a test spreadsheet which has more data to interrogate. I'm not sure what I have done wrong.

=MIN(FILTER(report1687265521957!$K$2:$K$15000,(report1687265521957!$A$2:$A$15000=$A4)*(report1687265521957!$K$2:$K$15000<>"")))

 

[KRice]

As written, I don't see any issues, although it is conceivable that the formula is correct but no matches are found. Does report1687265521957!$A$2:$A$15000 contain a list of reference codes? And on the worksheet where the formula appears, $A4 contains a reference code that you know will match at least one value in report1687265521957!$A$2:$A$15000? To confirm, find a known match and note the cell location (let's say the match is found in report1687265521957!$A$22). Then in an open cell on the formula sheet (where the $A4 reference is found), enter

report1687265521957!$A$22=$A4

and confirm TRUE is returned. Let me know what you get.

 

[xxrichbxx]

Hi @KRice the sheets I have referenced should definitely have data pulling through. The information attached earlier in the thread was just an extract of the original data.

 

[xxrichbxx]

quick update. I get it to work, but if there is no information then it returns #CALC!. How do I get it to return blank if there is nothing found?

I assume that #CALC! is essentially returning an error as it can't find anything.

 

[KRice]

Yes, the #CALC! error appears because the FILTER function is finding nothing. Otherwise FILTER returns an array containing all dates that satisfy the filter conditions, and then MIN or MAX operates on that array of values. The FILTER function does take a 3rd argument that describes what should be returned if no values are found, but in this case, I wouldn't use that feature---MIN or MAX will take that value and since the result is formatted as a date, you'll get a nonsensical answer, or if you tell FILTER to return a blank, MIN/MAX will still report a different error because they can't operate on a blank. The easiest approach is to simply wrap your existing formulas inside IFERROR functions, with instructions to return a blank...like this:

=IFERROR(MIN(FILTER($R$3:$R$12,($O$3:$O$12=$A3)*($R$3:$R$12<>""))),"")

 

[xxrichbxx]

Yes, the #CALC! error appears because the FILTER function is finding nothing. Otherwise FILTER returns an array containing all dates that satisfy the filter conditions, and then MIN or MAX operates on that array of values. The FILTER function does take a 3rd argument that describes what should be returned if no values are found, but in this case, I wouldn't use that feature---MIN or MAX will take that value and since the result is formatted as a date, you'll get a nonsensical answer, or if you tell FILTER to return a blank, MIN/MAX will still report a different error because they can't operate on a blank. The easiest approach is to simply wrap your existing formulas inside IFERROR functions, with instructions to return a blank...like this:

=IFERROR(MIN(FILTER($R$3:$R$12,($O$3:$O$12=$A3)*($R$3:$R$12<>""))),"")

That works perfectly. I just couldn't get the iferror function to work.

Thank you for all of your help.

 

[KRice]

Great! I'm happy to help.