Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: Data matching using datetime fields between 2 tables

  1. #1
    Seeker arevilo's Avatar
    Join Date
    Jan 2021
    Posts
    7
    Articles
    0
    Excel Version
    2016 office 365

    Data matching using datetime fields between 2 tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    308
    Articles
    0
    Excel Version
    2019
    upload a sample workbook with limited data and a mocked up solution. Do not post pictures as we cannot manipulate data in pictures.

  3. #3
    Seeker arevilo's Avatar
    Join Date
    Jan 2021
    Posts
    7
    Articles
    0
    Excel Version
    2016 office 365

    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
    Attached Files Attached Files

  4. #4
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  5. #5
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    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…
    Attached Files Attached Files
    Last edited by p45cal; 2021-01-30 at 12:31 PM.

  7. #7
    Seeker arevilo's Avatar
    Join Date
    Jan 2021
    Posts
    7
    Articles
    0
    Excel Version
    2016 office 365
    I have posted the question but no code on https://www.mrexcel.com/board/thread...ables.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

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    Well,
    Quote Originally Posted by p45cal View Post
    Can you check that the data is completely correct?

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,651
    Articles
    0
    Excel Version
    Office 365 Subscription
    And please add code tags to post #3, as requested earlier.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Seeker arevilo's Avatar
    Join Date
    Jan 2021
    Posts
    7
    Articles
    0
    Excel Version
    2016 office 365
    hi. please see post #7. many thanks.

Page 1 of 3 1 2 3 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •