Comparing date with unique id, and to test if they fall within the date range

Hello guys, I am currently working on a school project, where i have two files as below:

File 1: i have a list of unique id, and one unique id would have one or multiple dates with it (or you can think as the unique ID is the membership number, and the date is when they visit the gym, so every unique id could have multiple date assigned to it)

File 2: i have a list of unique id with start and end date, and same as above, one unique id could have multiple date ranges.

Therefore, with the above two file, i would need to know if there are any data from file 1 of each unique id, failed to fall into the date bucket (range) as recorded in file 2.

i am having a trouble when mapping the unique ID of both file while considering the dates and the ranges.

any help is greatly appreciated, thanks a lot!!!

Date Time

Join

Accepted answers

KAkuffo

hey @Ininsmelly please see my mock up here. if this solves please mark the post as a solution

Many Thanks

community solution_230822.yxmd

binu_acs

@Ininsmelly One way of doing this

MAtch_0823.yxmd

All comments

binu_acs

@Ininsmelly can you provide some sample data and expected output

Ininsmelly

An example of the data would be like the above, and the highlighted column would be the desired outcome. Thanks a lot!

Ininsmelly

Any solution for this🥲

KAkuffo

Hi @Ininsmelly in this situation i would utilise the Generate rows tool to create an entry for each unique id with every date within the range
Then using a join tool perform the join between the list of all dates and the actual entry dates, this way all matches between date rangers per ID will come out of the join and all mis matches will not then summarize and count per uniqueID or a formula tool to mark the check column "Y"

Mock up incoming but id this makes sense give it a try!

Ininsmelly

Hello @kakuffo, many thanks for your help, let me try with the generate row function. Thanks a lot!!!

KAkuffo

hey @Ininsmelly please see my mock up here. if this solves please mark the post as a solution

Many Thanks

community solution_230822.yxmd

binu_acs

@Ininsmelly One way of doing this

MAtch_0823.yxmd

Ininsmelly

Thank you guys, you guys are the legend!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703