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

[TABLE="width: 865"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Delivery Id[/TD]
[TD]Depot Name/ID[/TD]
[TD]Delivery Date[/TD]
[TD]Customer Timed Window start time[/TD]
[/TR]
[TR]
[TD="align: right"]45807069[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]45594568[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]45786459[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]45807069[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]07:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]45594568[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]07:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]45798812[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]11:00:00[/TD]
[/TR]
</tbody>[/TABLE]



Other file

[TABLE="width: 324"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]OdeKey[/TD]
[TD]EarlyDateTime[/TD]
[TD]Planned Arrived Date[/TD]
[/TR]
[TR]
[TD="align: right"]45807069[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]06:45:00[/TD]
[/TR]
[TR]
[TD="align: right"]45808544[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:15:00[/TD]
[/TR]
[TR]
[TD="align: right"]45777034[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:19:00[/TD]
[/TR]
[TR]
[TD="align: right"]45762002[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]12:02:00[/TD]
[/TR]
[TR]
[TD="align: right"]45803954[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:54:00[/TD]
[/TR]
[TR]
[TD="align: right"]45744899[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]14:13:00[/TD]
[/TR]
[TR]
[TD="align: right"]45811514[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]06:57:00[/TD]
[/TR]
[TR]
[TD="align: right"]45809058[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]07:04:00[/TD]
[/TR]
[TR]
[TD="align: right"]45776948[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]12:03:00[/TD]
[/TR]
[TR]
[TD="align: right"]45808842[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]12:10:00[/TD]
[/TR]
[TR]
[TD="align: right"]45798067[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]07:32:00[/TD]
[/TR]
[TR]
[TD="align: right"]45809065[/TD]
[TD="align: right"]18/02/2019[/TD]
[TD="align: right"]06:41:00[/TD]
[/TR]
[TR]
[TD="align: right"]45779368[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]11:51:00[/TD]
[/TR]
[TR]
[TD="align: right"]45812971[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]06:55:00[/TD]
[/TR]
[TR]
[TD="align: right"]45810989[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]11:19:00[/TD]
[/TR]
[TR]
[TD="align: right"]45811206[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]07:14:00[/TD]
[/TR]
[TR]
[TD="align: right"]45811203[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]06:52:00[/TD]
[/TR]
[TR]
[TD="align: right"]45811983[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]11:28:00[/TD]
[/TR]
[TR]
[TD="align: right"]45813965[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]07:23:00[/TD]
[/TR]
[TR]
[TD="align: right"]45814382[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]06:44:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[futurehead84]

Many thanks to you both for your replies.

Please find below a couple of examples of the data

[TABLE="width: 865"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Delivery Id (Kratzer Order Number)[/TD]
[TD]Depot Name/ID[/TD]
[TD]Delivery Date[/TD]
[TD]Actual Arrive Time Geofence/start job value[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD]-
[/TD]
[/TR]
</tbody>[/TABLE]


source data:

[TABLE="width: 550"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]idKey[/TD]
[TD]EarlyDate[/TD]
[TD]Planned Arrived [/TD]
[TD]arrived Time[/TD]
[TD]Departed Time[/TD]
[/TR]
[TR]
[TD="align: right"]45825086[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]14:11:00[/TD]
[TD="align: right"]13:48:00[/TD]
[TD="align: right"]13:48:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]13:23:00[/TD]
[TD="align: right"]13:06:00[/TD]
[TD="align: right"]13:06:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825041[/TD]
[TD="align: right"]23/02/2019[/TD]
[TD="align: right"]15:50:00[/TD]
[TD="align: right"]15:53:00[/TD]
[TD="align: right"]15:53:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825046[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]19:56:00[/TD]
[TD="align: right"]18:14:00[/TD]
[TD="align: right"]18:14:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825093[/TD]
[TD="align: right"]23/02/2019[/TD]
[TD="align: right"]16:40:00[/TD]
[TD="align: right"]16:41:00[/TD]
[TD="align: right"]16:41:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD="align: right"]20:32:00[/TD]
[TD="align: right"]20:23:00[/TD]
[TD="align: right"]20:23:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[wrightyrx7]

Many thanks to you both for your replies.

Please find below a couple of examples of the data

[TABLE="width: 865"]
<tbody>[TR]
[TD]Delivery Id (Kratzer Order Number)[/TD]
[TD]Depot Name/ID[/TD]
[TD]Delivery Date[/TD]
[TD]Actual Arrive Time Geofence/start job value[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


source data:

[TABLE="width: 550"]
<tbody>[TR]
[TD]idKey[/TD]
[TD]EarlyDate[/TD]
[TD]Planned Arrived[/TD]
[TD]arrived Time[/TD]
[TD]Departed Time[/TD]
[/TR]
[TR]
[TD="align: right"]45825086[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]14:11:00[/TD]
[TD="align: right"]13:48:00[/TD]
[TD="align: right"]13:48:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]20/02/2019[/TD]
[TD="align: right"]13:23:00[/TD]
[TD="align: right"]13:06:00[/TD]
[TD="align: right"]13:06:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825041[/TD]
[TD="align: right"]23/02/2019[/TD]
[TD="align: right"]15:50:00[/TD]
[TD="align: right"]15:53:00[/TD]
[TD="align: right"]15:53:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825046[/TD]
[TD="align: right"]19/02/2019[/TD]
[TD="align: right"]19:56:00[/TD]
[TD="align: right"]18:14:00[/TD]
[TD="align: right"]18:14:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825093[/TD]
[TD="align: right"]23/02/2019[/TD]
[TD="align: right"]16:40:00[/TD]
[TD="align: right"]16:41:00[/TD]
[TD="align: right"]16:41:00[/TD]
[/TR]
[TR]
[TD="align: right"]45825022[/TD]
[TD="align: right"]22/02/2019[/TD]
[TD="align: right"]20:32:00[/TD]
[TD="align: right"]20:23:00[/TD]
[TD="align: right"]20:23:00[/TD]
[/TR]
</tbody>[/TABLE]

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