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.
IF(List,REGEX_EXTRACT( List&"", "(?:" & SUBSTITUTE(Identifier,".", "\\.") & ")([^,]*)" ) )
|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!
@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.
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 &")","")
Any idea what I could be doing wrong here?
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.