Multi-Row Formula: Testing Against All Rows in Group?

[em1]

Looking for help using the multi-row formula (or a different tool) to test against all rows in a group. It's tricky because a) the groups all have varying amounts of records and b) I need to test against every record in the group, not just the preceding one.

Each record has a validity period (Period Start and Period End). I want to flag all records that have a validity period that overlaps with another record(s) in the group.

For example, in Group A, the second record is valid against the first because its Period Start occurs before the previous record's Period End. As a result, both are flagged.

In Group B, the last record does not overlap with any time periods of the other Group B records, so it does not get flagged. No records overlap in Groups C or D. In Group E, only the last 2 records overlap, so both are flagged.

Does anyone know if this might be possible? 

[JohnJPS]

[ ... edit: removed... ]

[CharlieS · Accepted solution]

Hello @em1 

I've put together the attached solution that achieves the desired result. The path I took was to generate a list of all days for each record and see which days had more than one record from the same group. One I determined which records had overlap with another, I joined that information back to the original table and cleaned it up.

Let me know if you have any questions.

[em1]

Thanks, @CharlieS !

This was exactly what I asked for. With a few tweaks it's running nicely to solve a bigger problem on the real data. Not sure how it will scale if the data grows much more but it definitely gets the job done!