I'm trying to detect overlaps between my records and display data from the respective other records they're overlapping with, but am struggling yet again with creating the right regular expression for the REGEX_EXTRACT.
Currently I'm...
- Creating an identifier field that helps recognize overlap (where identifier is equal = overlap).
- Creating an data field that includes the identifier and additional data that I want to display.
- Roll-up the data fields related to the linked record. Look up the roll-up on each record.
- Apply a REGEX_EXTRACT to the roll-up to extract the data I want to display.
IF(List,REGEX_EXTRACT(
List&"",
"(?:" & SUBSTITUTE(Identifier,".", "\\.") & ")([^,]*)"
)
)
Result:
- When record A & B overlap, it correctly shows the data of record A on record B and vice versa.
- When there's no overlap, it shows it's own Data and I cannot figure out how to avoid this. I'm trying to exclude itself from the list.
- To show multiple overlaps, I thought one specifies the number of occurrences by inserting the min/max in curly brackets {0,10} but always run into errors.
| Identifier | Data | List (rollup/arrayojoin of Data fields) | Desired output |
| 2022-06-13-Bashkim Memia-Host | 2022-06-13-Bashkim Memia-HostNikson: 12 pers +1 | 2022-06-13-Bashkim Memia-HostNikson: 12 pers +1,2022-06-13-Bashkim Memia-HostNedih: 2 pers +1 | Nedih: 2 pers +1 |
| 2022-06-13-Bashkim Memia-Host | 2022-06-13-Bashkim Memia-HostNedih: 2 pers +1 | 2022-06-13-Bashkim Memia-HostNikson: 12 pers +1,2022-06-13-Bashkim Memia-HostNedih: 2 pers +1 | Nikson: 12 pers +1 |
Thanks for your help!
