Formula to return TRUE for date closest to final date for each change in chainage

[AJLash]

Hi

Need some help from the brains trust. I have a spreadsheet that is the EXIF export from multiple image pickups over several captures at different dates (Col I) from a road where the images are aligned to road distance (Col E). I know for each picture what the date (Col K) is for when the best images should be kept. I need to identify the image for each Chainage Number (Col E) for which the date (Col I) is closest to the Best image Date (Col K). I have demonstrated a result in Col L.

Thankyou in advance

 

[mart37]

Map1
	A	B	C	D	E	F	G	H	I	J	K	L
1					Chainage				Date		DateFinalised	Best Image
2					90				7-2-2020		31-10-2019	WAAR
3					90				20-4-2020		31-10-2019	ONWAAR
4					100				7-2-2020		31-10-2019	WAAR
5					100				20-4-2020		31-10-2019	ONWAAR
6					110				3-10-2019		31-10-2019	ONWAAR
7					110				28-10-2019		31-10-2019	WAAR
8					110				20-4-2020		31-10-2019	ONWAAR
Blad1

Cell Formulas
Range	Formula
L2:L8	L2	=SUMPRODUCT(((SMALL(($E$2:$E$8=E2)*ABS($I$2:$I$8-$K$2:$K$8),COUNTIF($E$2:$E$8,"<>"&E2)+1)=(ABS($I$2:$I$8-$K$2:$K$8)*($E$2:$E$8=E2)))*ROW($E$2:$E$8)))=ROW()
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AJLash]

Map1
	A	B	C	D	E	F	G	H	I	J	K	L
1					Chainage				Date		DateFinalised	Best Image
2					90				7-2-2020		31-10-2019	WAAR
3					90				20-4-2020		31-10-2019	ONWAAR
4					100				7-2-2020		31-10-2019	WAAR
5					100				20-4-2020		31-10-2019	ONWAAR
6					110				3-10-2019		31-10-2019	ONWAAR
7					110				28-10-2019		31-10-2019	WAAR
8					110				20-4-2020		31-10-2019	ONWAAR
Blad1

Cell Formulas
Range	Formula
L2:L8	L2	=SUMPRODUCT(((SMALL(($E$2:$E$8=E2)*ABS($I$2:$I$8-$K$2:$K$8),COUNTIF($E$2:$E$8,"<>"&E2)+1)=(ABS($I$2:$I$8-$K$2:$K$8)*($E$2:$E$8=E2)))*ROW($E$2:$E$8)))=ROW()
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks Mark - works great. Would you be able to tweak it that if TRUE is returned and the image date is more than 10 days before the Date Finalised then a FALSE is returned?

 

[AJLash]

Found an issue with formula when down in the chainage 4160 the formula has no reliability and randomly calculates- any ideas?

 

[Peter_SSs]

@AJLash
In order to get the best answers & fastest I suggest for the future you attend to these two things:

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if these are any use

21 02 03.xlsm
	E				I		K	L	M
1	Chainage				Date		DateFinalised	Original Question	Tweak
2	90				7/02/2020		31/10/2019	TRUE	TRUE
3	90				20/04/2020		31/10/2019	FALSE	FALSE
4	100				7/02/2020		31/10/2019	TRUE	TRUE
5	100				20/04/2020		31/10/2019	FALSE	FALSE
6	110				3/10/2019		31/10/2019	FALSE	FALSE
7	110				20/10/2019		31/10/2019	TRUE	FALSE
8	110				20/04/2020		31/10/2019	FALSE	FALSE
TRUE

Cell Formulas
Range	Formula
L2:L8	L2	=ABS(K2-I2)=AGGREGATE(15,6,ABS(K$2:K$8-I$2:I$8)/(E$2:E$8=E2),1)
M2:M8	M2	=AND(K2-I2<11,ABS(K2-I2)=AGGREGATE(15,6,ABS(K$2:K$8-I$2:I$8)/(E$2:E$8=E2),1))