Merge and Change Text

[Heeby24]

Hi! I need help consolodating some data lines:

This is what I have:

And I'm hoing someone would be able to find a way to get what I want. In summary, I need to find entries where the only difference is the 'sent to' and 'from' and consolodate the text and time entry. Ideally It would say 'exchanged with' and then add the time entries together. Here's an example of what i'm doing manually:

Any guidance would be appreciated!

Many thanks,

 

[hagia_sofia]

This is a complicated attempt so let us hope somebody will figure out something more elegant (replace "date", "acitivity", and "time" ranges with your actual ranges):

=LET(
date,A5:A20,
activity,B5:B20,
time,C5:C20,
array,HSTACK(date,TEXTBEFORE(activity," ",1),TEXTBEFORE(TEXTAFTER(activity," ",1)," ",3),TEXTAFTER(activity," ",-2),time),
chain,BYROW(CHOOSECOLS(array,1,2,4),LAMBDA(a,CONCAT(a))),
count,HSTACK(UNIQUE(chain),MAP(UNIQUE(chain),LAMBDA(x,SUM(--(chain=x))))),
cases,XLOOKUP(chain,CHOOSECOLS(count,1),CHOOSECOLS(count,2)),
new_1,IF(cases=2,"Email exchanged with",CHOOSECOLS(array,3)),
calc,HSTACK(chain,CHOOSECOLS(array,5)),
sum,HSTACK(UNIQUE(chain),BYROW(UNIQUE(chain),LAMBDA(x,SUM(FILTER(CHOOSECOLS(calc,2),CHOOSECOLS(calc,1)=x))))),
new_2,XLOOKUP(chain,CHOOSECOLS(sum,1),CHOOSECOLS(sum,2)),
middle,MAP(CHOOSECOLS(array,2),new_1,CHOOSECOLS(array,4),LAMBDA(a,b,c,TEXTJOIN(" ",,a,b,c))),
UNIQUE(HSTACK(CHOOSECOLS(array,1),middle,new_2)))

 

[Heeby24]

This is a complicated attempt so let us hope somebody will figure out something more elegant (replace "date", "acitivity", and "time" ranges with your actual ranges):

=LET(
date,A5:A20,
activity,B5:B20,
time,C5:C20,
array,HSTACK(date,TEXTBEFORE(activity," ",1),TEXTBEFORE(TEXTAFTER(activity," ",1)," ",3),TEXTAFTER(activity," ",-2),time),
chain,BYROW(CHOOSECOLS(array,1,2,4),LAMBDA(a,CONCAT(a))),
count,HSTACK(UNIQUE(chain),MAP(UNIQUE(chain),LAMBDA(x,SUM(--(chain=x))))),
cases,XLOOKUP(chain,CHOOSECOLS(count,1),CHOOSECOLS(count,2)),
new_1,IF(cases=2,"Email exchanged with",CHOOSECOLS(array,3)),
calc,HSTACK(chain,CHOOSECOLS(array,5)),
sum,HSTACK(UNIQUE(chain),BYROW(UNIQUE(chain),LAMBDA(x,SUM(FILTER(CHOOSECOLS(calc,2),CHOOSECOLS(calc,1)=x))))),
new_2,XLOOKUP(chain,CHOOSECOLS(sum,1),CHOOSECOLS(sum,2)),
middle,MAP(CHOOSECOLS(array,2),new_1,CHOOSECOLS(array,4),LAMBDA(a,b,c,TEXTJOIN(" ",,a,b,c))),
UNIQUE(HSTACK(CHOOSECOLS(array,1),middle,new_2)))

Amazing! I think this worked I am getting a duplicate first name gerated in the activiy (i.e. Email from Joe Joe McDonald). Where do I make that change in the formula?

 

[hagia_sofia]

Thanks for the feedback - it is due to the fact that the number of spaces is different in "from" and "sent to". I tried to adresses it by inserting an additional space and trim it at the end, test this:

=LET(
date,A5:A20,
activity,B5:B20,
time,C5:C20,
array,HSTACK(date,TEXTBEFORE(activity," ",1),TEXTBEFORE(TEXTAFTER(SUBSTITUTE(activity,"from ","from  ")," ",1)," ",3),TEXTAFTER(activity," ",-2),time),
chain,BYROW(CHOOSECOLS(array,1,2,4),LAMBDA(a,CONCAT(a))),
count,HSTACK(UNIQUE(chain),MAP(UNIQUE(chain),LAMBDA(x,SUM(--(chain=x))))),
cases,XLOOKUP(chain,CHOOSECOLS(count,1),CHOOSECOLS(count,2)),
new_1,IF(cases=2,"Email exchanged with",CHOOSECOLS(array,3)),
calc,HSTACK(chain,CHOOSECOLS(array,5)),
sum,HSTACK(UNIQUE(chain),BYROW(UNIQUE(chain),LAMBDA(x,SUM(FILTER(CHOOSECOLS(calc,2),CHOOSECOLS(calc,1)=x))))),
new_2,XLOOKUP(chain,CHOOSECOLS(sum,1),CHOOSECOLS(sum,2)),
middle,MAP(CHOOSECOLS(array,2),new_1,CHOOSECOLS(array,4),LAMBDA(a,b,c,TEXTJOIN(" ",,a,b,c))),
UNIQUE(HSTACK(CHOOSECOLS(array,1),TRIM(middle),new_2)))