Data matching using datetime fields between 2 tables

[arevilo]

Hi All,

I have 2 tables of data: i. Orders Data and ii. Events Data.

In the Orders Data table the fields are: Customer_ID , Order_ID , Start_Datetime, End_Datetime

In the Events Data table the fields are: Customer_ID, Event_ID, Event_Datetime

There are many other fields but the above are the relevant ones. The Orders Data table is very messy and for that and other reasons I want to add the new columns into the Events Data table.

Essentially, I want to create 3 fields.
For each Customer_ID I want to:
1. concatenate all the Order_ID into one cell that meet the rule:Start_Datetime <= Event_Datetime <= End_Datetime

2. Count the number of orders that meet the rule above.

3. Identify the Order_ID where the (Start_Datetime <= Event_Datetime) AND has the smallest time difference. Ie. Is the closest order before the event occurred.


I would like the vba code please. Could we use arrays?
Is there faster ways in excel?

Any help is greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Data matching using datetime fields between 2 tables
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.