Need Automation that can pick a date from a dataset that fits between a date range

Question

So I get large datasets with lots of transactions, and sometimes I need to find dates within specific date ranges by account number. It would then need to output a list of account numbers with their transaction date that falls in the range.

So for example, I have Account 0001. Starting Date is 1/1/23 Ending Date is 6/7/23

I have a file with lots of transactions within and outside of that range. I need the automation to pick a date from within the file that fits within the range.

I would like to be able to input one excel file containing the list of transaction dates by account number, and another file containing the Starting Date and Ending Date.

I created two test input files. Can anyone help me figure this out? I've tried to find a similar situation but couldn't.

TransactionRangeSample.xlsx

TransactionDateSample.xlsx

binu_acs · Answer

@WishIKnewHowToCode  you are correct, the Generate row tool generates every date in between the dates, it is basically working like a loop, I think it is not a kind of brute-force match 😊. You can also use the append tool then filter out the records, but the appends tool works like cross-join, so have to be careful when you deal with huge records. I am also attaching the append tool method

DateRange_012523.yxmd

WishIKnewHowToCode · Answer

@binuacs I'm not exaggerating when I say this literally might save 100 hours of work for just this one client my team is working on. I've been working with your solution to make sure it works, and I think it does. I'm going to review it tomorrow with 1 or 2 other people to make sure it's doing what we need, but I think it's solid.

I've never used this generate row tool before, but basically I think it's just generating every date in between the ranges, and in a brute-force-y kind of way matches to my dates from my dataset. Correct me if I'm wrong on that.

binu_acs · Answer

@WishIKnewHowToCode One way of doing this

DateRange_012523.yxzp