I have a table of families and what each member is invited to. The table is sorted whereby the first row is the head of the family. (The summary column is a formula.)
How could I summarise this data by family, with the events column being a summary of all the events the family is invited to? The events column is alphabetically sorted. Expected Output:
You may try:
=let(Σ,scan(,A2:A,lambda(a,c,if(c="",a,a+1))),
map(unique(Σ),lambda(Λ,{textjoin(", ",1,filter(B2:B,Σ=Λ)),join(", ",sort(unique(tocol(split(textjoin(", ",1,filter(C2:C,Σ=Λ)),", ",0)))))})))