Find the Closest Date Match

[kdsanderson30]

I have been tasked to help with a tax project for my company and I am stuck.

We have a worksheet that lists multiple trucks, the date they filled their truck with fuel, gallons of fuel and various other data. The dilemma is we need to find the line for each Truck out of 21467 Lines that is closest to a specific date. So lets say column B list truck 000034 4 times with 4 separate dates, how do you pull the date closest to January 1. (the reports shows 2 weeks prior and 2 weeks after Jan 1) some trucks got gas 10 times during this period and others maybe one to five times.

Column A is a spacer for totals (from an import)

so the worksheet goes as follows:

B- Vehicle #
C- Vehicle Description
D- First Name
E- Last Name
F- Driver
G Trans Date (transaction)
H- Trans Time (transaction)
I- Odometer
J Gallons
K Dollars
L- Unit Cost
M Product (Unleaded or Diesel)
N Department
O Department level
P Business Unit
Q Product Line

 

[AhoyNC]

Maybe this will give you a starting point.
This is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F
1	Truck	Date		Closest	1/1/2015
2	1	1/12/2015
3	2	12/28/2014		Truck	Date
4	1	1/14/2015		1	12/28/2014
5	2	1/4/2015		2	1/4/2015
6	4	12/30/2014		3	12/29/2014
7	1	12/27/2014		4	12/30/2014
8	3	12/29/2014
9	4	1/12/2015
10	1	12/28/2014
11
CTRL-SHIFT-ENTER.

 

[CharlesChuckieCharles]

My approach would be to create a key of Vehicle# and Trans date in column R =B3&"_&G3
sort the dataset by R ascending


the use =INDEX(B5:R21467,IF(ISNA(MATCH(Z1,R5:R21467,0)),MATCH(Z1,R5:R21467,1),MATCH(Z1,R5:R21467,0)),10)

What this does is sees if there is an exact match for a vehicle on the day, and if not the next closest to the date (but Prior to the date such 31/12/14)

and returns column 10 (dollars)

where Z1 = the Vehicle Number and 01/01/15 looking like 05643_42005


This should work

 

[kdsanderson30]

Maybe this will give you a starting point.
This is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F
1	Truck	Date	*	Closest	1/1/2015	*
2	1	1/12/2015	*	*	*	*
3	2	12/28/2014	*	Truck	Date	*
4	1	1/14/2015	*	1	12/28/2014	*
5	2	1/4/2015	*	2	1/4/2015	*
6	4	12/30/2014	*	3	12/29/2014	*
7	1	12/27/2014	*	4	12/30/2014	*
8	3	12/29/2014	*	*	*	*
9	4	1/12/2015	*	*	*	*
10	1	12/28/2014	*	*	*	*
11	*	*	*	*	*	*
CTRL-SHIFT-ENTER.

When I tried this is gave me the answer of the truck #. I am not sure what I did wrong, but there is over 1500 trucks here so that one is kind of tricky.

 

[AhoyNC]

This was just an example, you would need to change to match your ranges. So, if your dates are in column G the Index range would need to be column G not B as in the example. You would also need to change the column A in my example to your column B if that's where the vehicle # is.

 

[kdsanderson30]

the purpose of this is to find the odometer reading for the vehicle closest to 1/1/15 out of a 4 week period 2 weeks prior and after 1/1/15 for tax purposes. I guess I was not very clear, but the example you gave would tell the closest date for the truck, but would not tell the ODO reading etc.

 

[AhoyNC]

see below.
You will need to make changes to ranges for your date.
The first part of the INDEX function will determine the data it brings in.

=INDEX([COLOR=#ff0000]$I$2:$I$10[/COLOR],MATCH(MIN(IF($B$2:$B$10=$B16,ABS($G$2:$G$10-$C$13))),IF($B$2:$B$10=$B16,ABS($G$2:$G$10-$C$13)),0))
Change part in red for data you want.

Formula can just be copied down if you are careful in how you lock the columns and rows.

It is an array function and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Vech. #	Vech Desc	FN	LN	Driver	Tran. Date	Tran, T	Odometer	Gal.
2		1	vech1				1/12/2015		125000
3		2	vech2				12/28/2014		67000
4		1	vech1				1/14/2015		130000
5		2	vech2				1/4/2015		75000
6		4	vech4				12/30/2014		88000
7		1	vech1				12/27/2014		102000
8		3	vech3				12/29/2014		80000
9		4	vech4				1/12/2015		105000
10		1	vech1				12/28/2014		102500
11
12
13		Closest	1/1/2015
14
15		Truck	Date	Odometer
16		1	12/28/14	102500
17		2	1/4/15	75000
18		3	12/29/14	80000
19		4	12/30/14	88000
20
CTRL-SHIFT-ENTER.

 

[Mike Szczesny]

Can you use this. I first found out the unique items. Then I used data validation for this item in my formula to list the dates for item 1. Then a formula to list all the ODO's for item . Then I created formulas find the nearest date and related ODO.

Excel 2012
	A	B	C	D	E	F	G
1	Item	Date	ODO	1/10/2015	Closest Date	01/07/15
2	1	01/07/15	100		ODO	100
3	2	12/02/14	75
4	3	02/23/15	100		Item	1
5	1	01/15/15	125
6	2	12/29/14	100		Date	ODO
7	3	3/12/215	200		01/07/15	100
8	2	01/17/15	150		01/15/15	125
9	1	01/14/15	120		01/14/15	120
10	2	01/03/15	160
11	3	01/17/15	75
12	3	01/06/15	50
13
14	1
15	2
16	3
Sheet1

Cell Formulas
Range	Formula
F1	{=MIN(IF($E$7:$E$12<=$D$1,E$7:E$12))}
F7	{=IFERROR(INDEX(C$2:C$12,SMALL(IF($A$2:$A$12=$F$4,ROW(C$2:C$12)-ROW(C$2)+1),ROWS(F$7:F7))),"")}
E7	{=IFERROR(INDEX(B$2:B$12,SMALL(IF($A$2:$A$12=$F$4,ROW(B$2:B$12)-ROW(B$2)+1),ROWS(E$7:E7))),"")}
A14	{=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF($A$2:$A$12<>"",MATCH($A$2:$A$12,$A$2:$A$12,0)),ROW($A$2:$A$12)-ROW($A$2)+1),ROW($A$2:$A$12)-ROW($A$2)+1),ROWS($A$14:A14))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kdsanderson30]

see below.
You will need to make changes to ranges for your date.
The first part of the INDEX function will determine the data it brings in.

=INDEX([COLOR=#ff0000]$I$2:$I$10[/COLOR],MATCH(MIN(IF($B$2:$B$10=$B16,ABS($G$2:$G$10-$C$13))),IF($B$2:$B$10=$B16,ABS($G$2:$G$10-$C$13)),0))
Change part in red for data you want.

Formula can just be copied down if you are careful in how you lock the columns and rows.

It is an array function and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	Vech. #	Vech Desc	FN	LN	Driver	Tran. Date	Tran, T	Odometer	Gal.
2	*	1	vech1	*	*	*	1/12/2015	*	125000	*
3	*	2	vech2	*	*	*	12/28/2014	*	67000	*
4	*	1	vech1	*	*	*	1/14/2015	*	130000	*
5	*	2	vech2	*	*	*	1/4/2015	*	75000	*
6	*	4	vech4	*	*	*	12/30/2014	*	88000	*
7	*	1	vech1	*	*	*	12/27/2014	*	102000	*
8	*	3	vech3	*	*	*	12/29/2014	*	80000	*
9	*	4	vech4	*	*	*	1/12/2015	*	105000	*
10	*	1	vech1	*	*	*	12/28/2014	*	102500	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*
13	*	Closest	1/1/2015	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	Truck	Date	Odometer	*	*	*	*	*	*
16	*	1	12/28/14	102500	*	*	*	*	*	*
17	*	2	1/4/15	75000	*	*	*	*	*	*
18	*	3	12/29/14	80000	*	*	*	*	*	*
19	*	4	12/30/14	88000	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*
CTRL-SHIFT-ENTER.

this worked PERFECTLY!!!! thank you so much for your very valuable time! It was VERY MUCH APPRECIATED!!! thank you!!!

 

[AhoyNC]

You're welcome. Glad that worked for you. Thanks for the feedback.