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

[Ininsmelly]

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

[binuacs]

@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 · Accepted solution]

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

Many Thanks

[binuacs · Accepted solution]

@Ininsmelly One way of doing this

[Ininsmelly]

Thank you guys, you guys are the legend!