Skip to main content
Stack Overflow for Teams is now Stack Internal: See how we’re powering the human intelligence layer of enterprise AI. Read more >

Return to Answer

added 311 characters in body
Source Link
P.b
P.b
  • 12k
  • 2
  • 15
  • 34

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

For older Excel I'd recommend a helper column to get the unique names, for instance using this formula in D4 dragged down: =IFNA(INDEX(B$4:B$18,MATCH(TRUE,ISNA(MATCH(B$4:B$18,D$3:D3,0)),0)),"")

In E4 refer to D4 like: =IF(LEN(D4),D4&"( "&TEXTJOIN(", ",,IF(B$4:B$18=D4,C$4:C$18,""))&" )","")

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

For older Excel I'd recommend a helper column to get the unique names, for instance using this formula in D4 dragged down: =IFNA(INDEX(B$4:B$18,MATCH(TRUE,ISNA(MATCH(B$4:B$18,D$3:D3,0)),0)),"")

In E4 refer to D4 like: =IF(LEN(D4),D4&"( "&TEXTJOIN(", ",,IF(B$4:B$18=D4,C$4:C$18,""))&" )","")

added 152 characters in body
Source Link
P.b
P.b
  • 12k
  • 2
  • 15
  • 34

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))

Or judging from your screenshot (different from your question): =TOCOL(BYCOL(L3:T18,LAMBDA(b,@+b&"( "&ARRAYTOTEXT(DROP(FILTER(b,LEN(b)),1))&" )")))

Source Link
P.b
P.b
  • 12k
  • 2
  • 15
  • 34

Using M365: =LET(u,UNIQUE(B4:B18),MAP(u,LAMBDA(m,m&"( "&ARRAYTOTEXT(FILTER(C4:C18,B4:B18=m))&" )")))