Data matching using datetime fields between 2 tables

Hi p45cal,

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


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.
 
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.
 

Attachments

  • ExcelGuru11028Testing_Workbook.xlsx
    146.6 KB · Views: 5
Last edited:
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.
 

Attachments

  • ExcelGuru11028Testing_Workbook Macro adjusted.xlsm
    150.9 KB · Views: 12
Back
Top