Help with regular expression for REGEX_EXTRACT

[Ricardo]

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

1. Creating an identifier field that helps recognize overlap (where identifier is equal = overlap).
2. Creating an data field that includes the identifier and additional data that I want to display.
3. Roll-up the data fields related to the linked record. Look up the roll-up on each record.
4. Apply a REGEX_EXTRACT to the roll-up to extract the data I want to display.

IF(List,REGEX_EXTRACT(
     List&"",
     "(?:" & SUBSTITUTE(Identifier,".", "\\.") & ")([^,]*)"
     )
)

Result:

1. When record A & B overlap, it correctly shows the data of record A on record B and vice versa.
2. 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.
3. 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!

[Andrey_Kovalev]

@Ricardo , hi! You may try the following formula:

SUBSTITUTE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(Overlap,"("& Data &")",""),"("& ID &")",""),"(^,)|(,$)",""),",,",",")

I assumed all the fields as single line text, so you will need to add '&"' to ensure strings. Also, the rollup field (Overlap) can have unlimited entries. Hope this helps.

[Ricardo]

Thanks @Andrey_Kovalev! 

The innermost REGEX_REPLACE errors on the Overlap, which is a lookup of the rolled up / ARRAYJOIN(values) of the Data fields, I edited my post for clarify, I probably misplained something. After modifying the formula to Overlap&"" (for string) it gets going. 

REGEX_REPLACE(Overlap&"","("& Data &")","")

1. Strangely, the first REGEX_REPLACE still doesn't remove Data from the string and outputs the original/unchanged Overlap. 
2. The second REGEX_REPLACE removes the ID correctly.

Any idea what I could be doing wrong here?

[Andrey_Kovalev]

Ensure the Data is a string also, attach '&""'.

[Ricardo]

I tried and it didn't work, this is where I got stuck. 😕

The "Overlap" (Data rollup) actually includes all and not just overlapping records. This is why I initially used the REGEX_EXTRACT together with Identifier to select relevant ones. 

The final formula would be a combination of both, removing its own string from the roll-up, extracting all occurrences with matching IDs and then continuing to remove Identifier strings to get the desired output.

[Andrey_Kovalev]

If you give more sample data I could try to search for a solution.

[Ricardo]

Thanks for the offer. I'm not sure what's the best way to share data. I synced some sample data into a shared view. Does this do the trick?