Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

[Nic001]

Hi everyone,

My Data in Column A has linked pairs (e.g [C-D]). The amount of pairs per row varies between 1 and 10 pairs:

In [A1]: [A-B, C-D, A-E], data which I want to simplify into : -> [A-B-E, C-D]
In [A2]: [C-D, G-A, G-B, F-E] -> [C-D, G-A,B, F-E]
In [A3]: [A-B, B-F, E-F, H-I] -> [A-B-F-E, H-I]
In [A4]: [F-G, H-G] -> [F-G-H]
In [A5]: ...


Can I bundle those pairs, and show groups of relations instead (e.g [A-B, B-F, E-F, H-I] -> [A-B-F-E, H-I])
Can anyone inform me how I can tackle this?

See excel-attachment for a similar, more detailed example..

Thank you in advance,
With friendly greetings,
N.

[Bo_Ry]

This requires MS365 update with LAMBDA helper functions

https://techcommunity.microsoft.com/...a/ba-p/3073293

Please try

=LET(z,B6,y,FILTERXML("<x><m>"&SUBSTITUTE(z,", ","</m><m>")&"</m></x>","//m"),
r,REDUCE(z,y,LAMBDA(a,v,LET(t,IFERROR(FILTERXML("<x><m>"&SUBSTITUTE(a,", ","</m><m>")&"</m></x>","//m"),""),l,LEFT(v,FIND("-",v)-1),TEXTJOIN(", ",0,IF(COUNT(FIND(l,t))>1,SUBSTITUTE(IF(v=y,"",t),l,v),t))))),
SUBSTITUTE(TRIM(SUBSTITUTE(r,",",))," ",", "))

[Nic001]

Hi B.,

Thank you for your Lambda function. This certainly IS the way forward.
I am now trying to make sense of what you did here, how it works... [Alt+t+u+f, the Evaluate Formula]

I did see imperfections in your attachment (12.2 KB):
[C10] Dirk-Casper-Hilde, Fiona-Gerard-Isabel-Eva-Hilde) -> The formula did not link this as one group
[C11] Boris-Anna, Isabel-Anna, Eva-Hilde, Dirk-Casper -> The function missed to combine Boris-Anna, Isabel-Anna into Boris-Anna-Isabel).

I now can probably try to figure out how to improve and make this 100%.
Thank you. Amazing how you came up with that, and so quick as well.

With friendly greetings,
N.

[Bo_Ry]

Massive formula

=LET(z,B6,c,", ",d,"-",xm,"<x><m>",mm,"</m><m>",mx,"</m></x>",m,"//m",y,FILTERXML(xm&SUBSTITUTE(z,c,mm)&mx,m),
r,REDUCE(z,y,LAMBDA(a,v,LET(t,IFERROR(FILTERXML(xm&SUBSTITUTE(a,c,mm)&mx,m),""),x,XLOOKUP(v,y,t),
l,LEFT(v,FIND(d,v)-1),r,MID(v,LEN(l)+2,99),TEXTJOIN(c,0,IF(COUNT(FIND(l,t))>1,SUBSTITUTE(IF(v=y,"",t),l,x),IF(COUNT(FIND(r,t))>1,SUBSTITUTE(IF(v=y,"",t),r,x),t)))))),
s,SUBSTITUTE(TRIM(SUBSTITUTE(r,",",))," ",c),t,TRANSPOSE(FILTERXML(xm&SUBSTITUTE(s,c,mm)&mx,m)),l,MAX(LEN(t)),n,MAX(LEN(t)-LEN(SUBSTITUTE(t,d,))+1),
TEXTJOIN(c,,BYCOL(TRIM(MID(SUBSTITUTE(t,d,REPT(" ",l)),SEQUENCE(n,,,l),l)),LAMBDA(w,TEXTJOIN(d,,SORT(UNIQUE(w)))))))

or with new TEXTSPLIT only available for Office insider
https://techcommunity.microsoft.com/...s/ba-p/3186066

=LET(z,B6,c,", ",d,"-",y,TEXTSPLIT(z,,c),
r,REDUCE(z,y,LAMBDA(a,v,LET(t,IFERROR(TEXTSPLIT(a,,c),""),x,XLOOKUP(v,y,t),
l,LEFT(v,FIND(d,v)-1),r,MID(v,LEN(l)+2,99),TEXTJOIN(c,0,IF(COUNT(FIND(l,t))>1,SUBSTITUTE(IF(v=y,"",t),l,x),IF(COUNT(FIND(r,t))>1,SUBSTITUTE(IF(v=y,"",t),r,x),t)))))),
t,TRANSPOSE(TEXTSPLIT(r,d,c,,"")),TEXTJOIN(c,,BYCOL(t,LAMBDA(w,TEXTJOIN(d,,SORT(UNIQUE(w)))))))

[Nic001]

Hi B.,

The massive formula works perfectly fine... when adding 2 missing closing parentheses. ))
Eyebrow-raising and wonderful to see how evaluates. ^^

This problem is [SOLVED].
Thank you.

With friendly greetings,
N.

[Nic001]

Hi,


(Un)Fortunately I stumbled upon an example that returns an imperfection:

Dirk-Anna, Dirk-Boris, Casper-Anna, Fiona-Casper -> returns: Dirk-Anna-Boris, Fiona-Casper-Anna (The formula did not link this as one group)

So this problem is [not Solved] yet. Can anyone come up with a 100% solution?


With friendly greetings,
N.

[AliGW]

Please provide an updated workbook highlighting the issue. Are there any other possible combinations you have not told us about?

[Nic001]

Hi A,


The updated workbook attached here contains about a dozen 'simplified' examples.
(My personal workbook contains 1000+ rows of these 'couplets').


With friendly greetings,
N.

[AliGW]

I think you probably need to tell us what is the MAXIMUM number of individual names that could/can end up in a family group. Is it FOUR or could it be more? If more, how many more? What's the maximum number of pairs that can/could exist in any cell that wuld need processing by the formula?

[Nic001]

Hi A.,


You are right. That would make the issue more clear.

The maximum amount of individual names is 9 (A,B,C,D,E,F,G,H,I)

The theoretical maximum amount of couplets in a single row is 13. (practically, on average, there are about 4 couplets, sometimes there are up to 6 couplets in a single row, rarely there are more then 8, but the theoretical maximum is 13)


With friendly greetings,
N.

[Bo_Ry]

Please try

=LET(z,B6,c,", ",d,"-",xm,"<x><m>",mm,"</m><m>",mx,"</m></x>",m,"//m",u,SORT(UNIQUE(FILTERXML(xm&SUBSTITUTE(SUBSTITUTE(z,d,c),c,mm)&mx,m)),,-1),
REDUCE(z,u,LAMBDA(a,v,LET(t,FILTERXML(xm&SUBSTITUTE(a,c,mm)&mx,m),f,ISNUMBER(FIND(v,t)),TEXTJOIN(c,,TEXTJOIN(d,,v,SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(FILTER(t,f,""),v,),d," "))," ",d)),FILTER(t,1-f,""))))))

[Nic001]

Hi B.,


Thank you, it does work.
Functions used in your formula: LET, SORT, UNIQUE, FILTERXML, SUBSTITUTE, LAMBDA, ISNUMBER, TEXTJOIN, TRIM, FILTER

Amazing. Thank you.

The problem is [SOLVED].


With friendly greetings,
N.