Data matching using datetime fields between 2 tables

A

arevilo

New member
Joined
Jan 29, 2021
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016 office 365
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!
yH5BAEAAAAALAAAAAABAAEAAAIBRAA7
 
upload a sample workbook with limited data and a mocked up solution. Do not post pictures as we cannot manipulate data in pictures.
 
Vba code sample to be optimised to rund more efficiently/faster

This is the code I have so far which does the job but is a bit slow to my liking.. Is it possible to speed it up using arrays? Also, is it possible to add another column which has the maximum number of orders per customer_id. Lastly, the code concatenates the orders_ids as it reads from line to line but I also want the final value of all the order_IDs together. eg. if it has a maximum of 9 orders then all nine orders are listed.

Code: 
Sub Order_Ids()


Dim i As Long
Dim j As Long
Dim Order As String
Dim Encounter As Long
Dim Count As Integer
Dim Delta As Double
Dim Start As Long


i = 2
j = 2


While Sheets("EVENT DATA").Cells(i, 1) <> ""


'    If Sheets("EVENT DATA").Cells(i, 10) = 1 Or Sheets("EVENT DATA").Cells(i, 11) = 1 Then
    If Sheets("EVENT DATA").Cells(i, 1) <> Sheets("EVENT DATA").Cells(i - 1, 1) Then
        Encounter = Sheets("EVENT DATA").Cells(i, 1)
        
        Do Until Sheets("ORDERS").Cells(j, 9) >= Encounter
            j = j + 1
            If Sheets("ORDERS").Cells(j, 9) = "" Then
                Exit Do
            End If
        Loop
        Start = j
     End If
        Order = ""
        Count = 0
        Delta = 0
        j = Start
        While Sheets("ORDERS").Cells(j, 9) = Encounter
        
            If (Sheets("ORDERS").Cells(j, 1) <= Sheets("EVENT DATA").Cells(i, 4)) And (Sheets("EVENT DATA").Cells(i, 4) <= Sheets("ORDERS").Cells(j, 2)) Then
                If Order = "" Then
                 Order = Sheets("ORDERS").Cells(j, 7)
                 Count = 1
                 Else
                 Order = Order & "_" & Sheets("ORDERS").Cells(j, 7)
                
                 Count = Count + 1
                End If
                If Sheets("ORDERS").Cells(j, 19) <= Sheets("EVENT DATA").Cells(i, 4) And Sheets("ORDERS").Cells(j, 28) = "Auth (Verified)" Or Sheets("ORDERS").Cells(j, 28) = "Modified/Amended/Cor" And Sheets("ORDERS").Cells(j, 16) = "Completed" Then
                    If Delta = 0 Then
                    Sheets("EVENT DATA").Cells(i, 17) = Sheets("ORDERS").Cells(j, 7)
                    Delta = Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)
                    Else
                        If (Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)) < Delta Then
                        Sheets("EVENT DATA").Cells(i, 17) = Sheets("ORDERS").Cells(j, 7)
                        Delta = Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)
                        End If
                    End If
                End If
            End If
                
            j = j + 1
        
        Wend
        Sheets("EVENT DATA").Cells(i, 15) = Order
        Sheets("EVENT DATA").Cells(i, 16) = Count
'    End If
    
    
    i = i + 1


Wend




End Sub
 

Attachments

  • Testing_Workbook.xlsm
    140.5 KB · Views: 12
Hi and welcome
please wrap your code with code tags ( select the code and click the #button)
Thanks

Note to all users : no help to be offered until tags have been added - thanks
 
Hi and welcome
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question
 
In the attached there's a table in the EVENT DATA sheet at cell S1.
Can you check that the data is completely correct?
Column V contains the Order Ids in date order with Last Order at the start.
There is no code or query in the attachment; that table has been created using Power Query.

When you've done those things that Pecoflyer has asked I'll be allowed to give more details…
 

Attachments

  • ExcelGuru11028Testing_Workbook-CheckCorrect.xlsx
    142 KB · Views: 16
Last edited:
I have posted the question but no code on https://www.mrexcel.com/board/threads/data-matching-using-datetime-fields-between-2-tables.1160022/

Code: 
Sub Order_Ids()




Dim i As Long
Dim j As Long
Dim Order As String
Dim Encounter As Long
Dim Count As Integer
Dim Delta As Double
Dim Start As Long




i = 2
j = 2




While Sheets("EVENT DATA").Cells(i, 1) <> ""




' If Sheets("EVENT DATA").Cells(i, 10) = 1 Or Sheets("EVENT DATA").Cells(i, 11) = 1 Then
If Sheets("EVENT DATA").Cells(i, 1) <> Sheets("EVENT DATA").Cells(i - 1, 1) Then
Encounter = Sheets("EVENT DATA").Cells(i, 1)


Do Until Sheets("ORDERS").Cells(j, 9) >= Encounter
j = j + 1
If Sheets("ORDERS").Cells(j, 9) = "" Then
Exit Do
End If
Loop
Start = j
End If
Order = ""
Count = 0
Delta = 0
j = Start
While Sheets("ORDERS").Cells(j, 9) = Encounter


If (Sheets("ORDERS").Cells(j, 1) <= Sheets("EVENT DATA").Cells(i, 4)) And (Sheets("EVENT DATA").Cells(i, 4) <= Sheets("ORDERS").Cells(j, 2)) Then
If Order = "" Then
Order = Sheets("ORDERS").Cells(j, 7)
Count = 1
Else
Order = Order & "_" & Sheets("ORDERS").Cells(j, 7)


Count = Count + 1
End If
If Sheets("ORDERS").Cells(j, 19) <= Sheets("EVENT DATA").Cells(i, 4) And Sheets("ORDERS").Cells(j, 28) = "Auth (Verified)" Or Sheets("ORDERS").Cells(j, 28) = "Modified/Amended/Cor" And Sheets("ORDERS").Cells(j, 16) = "Completed" Then
If Delta = 0 Then
Sheets("EVENT DATA").Cells(i, 17) = Sheets("ORDERS").Cells(j, 7)
Delta = Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)
Else
If (Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)) < Delta Then
Sheets("EVENT DATA").Cells(i, 17) = Sheets("ORDERS").Cells(j, 7)
Delta = Sheets("EVENT DATA").Cells(i, 4) - Sheets("ORDERS").Cells(j, 19)
End If
End If
End If
End If


j = j + 1


Wend
Sheets("EVENT DATA").Cells(i, 15) = Order
Sheets("EVENT DATA").Cells(i, 16) = Count
' End If




i = i + 1




Wend








End Sub
 
hi. please see post #7. many thanks.
 
Please see post #9.

Note: no further help to be offered here until the OP has complied with the request for code tags to be added, please.
 
Hi P45cal, the last order field isn't correct. Did you round up/truncate to minutes only? However, the orders field seems correct. How was that done?
If possible, do you also know how to write vba array code?

Well,
quote_icon.png
Originally Posted by p45cal
Can you check that the data is completely correct?


 
Last edited:
Why do you continue to ignore the instruction to add code tags to post #3? Please do NOT ignore moderation requests. Thanks.
 
arevilo said:
the last order field isn't correct.
Click to expand...
Yes, it's quite different.
I'm not allowed to post any solution/offering at the moment, but to further my understanding:
Regarding the Last Order, you said: "Ie. Is the closest order before the event occurred.". To me that means, because every event is between StartDate and EndDate, you can only look at StartDate for closeness.
Heree's a picture of the data for your first line of data in the EVENT ORDER sheet:
View attachment 10307
2021-01-31_131734.png
(clicking it should enlarge it if it's too small)I would have thought that the 9:46 was closer/later then the 8:47 to the event date/time of 12:35 (that's pm), no? In that case the Last Order ends in 613, which is what I have,but you have the other.
I'll look at your vba code to check what's happening there.


arevilo said:
How was that done?
Click to expand...
PowerQ Query


arevilo said:
If possible, do you also know how to write vba array code?
Click to expand...
Yes, I can and will but have to hang back at the moment.
 
Last edited:
AliGW said:
Why do you continue to ignore the instruction to add code tags to post #3? Please do NOT ignore moderation requests. Thanks.
Click to expand...
Is this possible ?
Are normal members of the forum no longer restricted to the 10 minute time limit for editing posts ?
 
That's a good point, however the OP hasn't even had the courtesy to respond to the moderation request. If he does and says he is unable to do it himself, then I shall happily do it for him, but there's a principle here: you don't just ignore repeated messages from moderators.
 
Just now I was not able to edit my previous post so I think post #7 by the OP was a very reasonable response to what was requested.

PS: this isn't something that I should be asking in the thread but I did pm you quite some time ago with the same concern and your pm back also was pretty much "I don't know"
 
I've found a PM from you from two years ago - must confess, I had forgotten all about it - sorry.

Your point about post #7 is valid - I had read it differently. I've now added the code tags for the OP.
 
Looking at your vba code, I'm assuming that the variable Delta is a holding variable for the smallest time difference? If so, the code looks at
Code: 
Sheets("ORDERS").Cells(j, 19)
which is column S of the ORDERS sheet which is completely empty in the file you attached.
That may explain the discrepency.
My PQ solution effectively looked at Sheets("ORDERS").Cells(j, 1) instead.
I'll await comment before posting incorrect code.
 
Hi AliGW,

Thanks for adding the code qualifiers. This is the first time I've ever posted on a forum for help and was aware of protocol. I wasn't sure how to modify previous post so I had posted again (#7) with the code qualifiers thinking that would be sufficient. Apologies for any inconveniences this may have caused.

AliGW said:
Why do you continue to ignore the instruction to add code tags to post #3? Please do NOT ignore moderation requests. Thanks.
Click to expand...
 
You must log in or register to reply here.
Back
Top