How to match on both ID and Date from another sheet

[futurehead84]

Hi there,

I am attempting to separate values for the same ID from different dates.

Basically on the earlier date the delivery failed and was redelivered on a later date.

Obviously this means the details of the delivery are different for each date.

I have tried vlookup but am only able to get the value for the first date as the IDs are the same for the different dates.

I have heard about the MATCH function, but am unsure how to use it especially across different sheets.

Any help would be appreciated

Thanks

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[wrightyrx7]

I agree with Special-K99 its difficult to help without seeing some sort of test data.

OR on BOTH sheets create a unique ID by concatenating the ID&DeliveryDate << do a vlookup using this.

 

[futurehead84]

Hi thanks for your replies.

Please find below samples from both files I need data for

Delivery Id	Depot Name/ID	Delivery Date	Customer Timed Window start time
45807069	31	18/02/2019	07:00:00
45594568	31	18/02/2019	07:00:00
45786459	31	18/02/2019	07:00:00
45807069	31	21/02/2019	07:00:00
45594568	31	20/02/2019	07:00:00
45798812	31	18/02/2019	11:00:00

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

Other file

OdeKey	EarlyDateTime	Planned Arrived Date
45807069	18/02/2019	06:45:00
45808544	18/02/2019	07:15:00
45777034	18/02/2019	07:19:00
45762002	18/02/2019	12:02:00
45803954	18/02/2019	07:54:00
45744899	18/02/2019	14:13:00
45811514	18/02/2019	06:57:00
45809058	18/02/2019	07:04:00
45776948	18/02/2019	12:03:00
45808842	19/02/2019	12:10:00
45798067	19/02/2019	07:32:00
45809065	18/02/2019	06:41:00
45779368	21/02/2019	11:51:00
45812971	19/02/2019	06:55:00
45810989	19/02/2019	11:19:00
45811206	19/02/2019	07:14:00
45811203	20/02/2019	06:52:00
45811983	20/02/2019	11:28:00
45813965	19/02/2019	07:23:00
45814382	19/02/2019	06:44:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[futurehead84]

Many thanks to you both for your replies.

Please find below a couple of examples of the data

Delivery Id (Kratzer Order Number)	Depot Name/ID	Delivery Date	Actual Arrive Time Geofence/start job value
45825022	43	20/02/2019	-
45825022	43	22/02/2019	-

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

source data:

idKey	EarlyDate	Planned Arrived	arrived Time	Departed Time
45825086	21/02/2019	14:11:00	13:48:00	13:48:00
45825022	20/02/2019	13:23:00	13:06:00	13:06:00
45825041	23/02/2019	15:50:00	15:53:00	15:53:00
45825046	19/02/2019	19:56:00	18:14:00	18:14:00
45825093	23/02/2019	16:40:00	16:41:00	16:41:00
45825022	22/02/2019	20:32:00	20:23:00	20:23:00

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[wrightyrx7]

Many thanks to you both for your replies.

Please find below a couple of examples of the data

Delivery Id (Kratzer Order Number)	Depot Name/ID	Delivery Date	Actual Arrive Time Geofence/start job value
45825022	43	20/02/2019	-
45825022	43	22/02/2019	-

<tbody>
</tbody>

source data:

idKey	EarlyDate	Planned Arrived	arrived Time	Departed Time
45825086	21/02/2019	14:11:00	13:48:00	13:48:00
45825022	20/02/2019	13:23:00	13:06:00	13:06:00
45825041	23/02/2019	15:50:00	15:53:00	15:53:00
45825046	19/02/2019	19:56:00	18:14:00	18:14:00
45825093	23/02/2019	16:40:00	16:41:00	16:41:00
45825022	22/02/2019	20:32:00	20:23:00	20:23:00

<tbody>
</tbody>

If you ONLY want the latest date for an ID you could try this

=MAX(IF(A:A=A2,B:B))

*this formula requires you to press Ctrl + Shift + Enter