Google Sheets - merge duplicates "join" alternative

Question

CONTEXT:

• I have a script that fetches some infos from gmail - "Emails Received" sheet - like from, cc, subject, to.
• Sometimes the email subject isn't exactly the same but what's important it's the reference always indicated on the subject - e.g. 9052/18.6T8TER in subject: "Re: payment proof 9052/18.6T8TER".
• I've created another sheet called "Emails Joined" that extracts that important reference from the subjects on Column A; merges the remaining data that belongs to all emails that have that same reference within email subjects
• Using "Join" as is now, Array doesn't work and it makes the script a lot slower when it's fetching the emails as, I assume, it's always trying to join the results.

WHAT I'M LOOKING FOR

• A better alternative to Join, keeping in mind that I don't want to match the full subject but instead the reference that's contained within the email subjects
• An alternative that doesn't make the script so slow but if that's not possible, I also saw some posts about using If to "stop" a formula and maybe that's the way to go so the merge doesn't interfere with the email fetching
• Can anyone point me in a better direction?

Thanks in advance.

Test Spreadsheet

Answer 1

Take a look at the new tab called MK.help. 3 formulas in B2,D2 and E2. This is the one for B2, it's based on a concept I learned here on Stack from @Player0.

=ARRAYFORMULA(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY({REGEXEXTRACT('Emails Received'!C2:C&"|";"[A-Z]\D*([ \d|/\(.|_)\d A-Z+]+)\[?")\CHAR(9679)&TO_TEXT('Emails Received'!D2:D)&CHAR(10)};"select MAX(Col2) group by Col2 pivot Col1");;9^9)));"|"))