I am looking for an Excel 365 formula to join data together in a single cell by a category and the data to be further broken out buy a subcategory on separate lines within the category cell. Please see my example data and results. I would be grateful for any help with formula ideas.
example data
results
I attempted to use TEXTJOIN and UNIQUE functions, but was not sure how to embed the multiple categories TEXTJOIN within a formula.
You could try something along the lines :
• Formula used in cell F18
=LET(x,FILTER($B$2:$C$16,$A$2:$A$16=E18),
y,DROP(x,,-1),z,DROP(x,,1),u,UNIQUE(y),
TEXTJOIN(CHAR(10),,MAP(u,LAMBDA(m,m&" - "&ARRAYTOTEXT(FILTER(z,m=y))))))
Or, you could spill the formula for the whole range
• Formula used in cell F18
=DROP(REDUCE(0, E18:E20, LAMBDA(a,b, VSTACK(a,
LET(f, FILTER($B$2:$C$16, $A$2:$A$16 = b),
y, DROP(f, , -1), z, DROP(f, , 1), u, UNIQUE(y),
TEXTJOIN(CHAR(10), , MAP(u, LAMBDA(m, m & " - " & ARRAYTOTEXT(FILTER(z, m = y))))))))), 1)
Note: Select the formulated range and wrap the cells, by hitting ALT+H+W.
Relatively alike to the other answer:
Formula in A18:
=REDUCE({"Category","Results"},UNIQUE(A2:A16),LAMBDA(a,b,VSTACK(a,HSTACK(b,TEXTJOIN(CHAR(10),,MAP(UNIQUE(FILTER(B2:B16,A2:A16=b)),LAMBDA(x,x&" - "&TEXTJOIN(",",,FILTER(C2:C16,(A2:A16=b)*(B2:B16=x))))))))))
