Page 3 of 3 FirstFirst 1 2 3
Results 21 to 23 of 23

Thread: Data matching using datetime fields between 2 tables

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


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

    Hi p45cal,

    column 19 did have a datetime field and on further thought, column 1 would be more appropriate. Please go ahead.


    Quote Originally Posted by p45cal View Post
    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.

  2. #22
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    Attached is the Power Query version. Right-click the table at cell S1 and choose Refresh to update it.

    The first refresh might take a few seconds, but subsequent ones will be faster.
    Whether this is faster than you macro I doubt, but if your real data contain a huge number of rows it might become so.

    ps. note that there's no accounting for Auth (Verified) , Modified/Amended/Cor and Completed in other columns this file - it wasn't in your earlier narrative, I only saw it in your macro.
    Attached Files Attached Files
    Last edited by p45cal; 2021-02-01 at 11:05 AM.

  3. #23
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    In the attached is a macro Order_Ids2 which is your with adjustments to bring the calculations in-memory by using arrays as you suggested.
    There's a button at cell S1 of the EVENTS DATA sheet which calls it. I've adjusted this one to look at column 1 instead of column 19 while leaving the original code lines commented-out.
    I've made no attempt at all to look at the logic/algorithm of your macro - just translated it.
    Columns W, X & Y are there for comparisons with your macro, which is still there and should run without conflict. Of course Column Y is showing many FALSE because of the change to look at column 1 instead of 19.
    Attached Files Attached Files

Page 3 of 3 FirstFirst 1 2 3

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
  •