Can someone tell me how to remove the spaces between the values. I can't workout if or how the JOIN function should be applied?
The desired outcome should be like: VALUE,VALUE,VALUE
Not: VALUE, VALUE, VALUE
Google Sheet to play with - Cell i2 contains the formula
try:
=ARRAYFORMULA(SUBSTITUTE(UNIQUE(ARRAY_CONSTRAIN({B2:B,
SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(B2:B=TRANSPOSE(B2:B),
TRANSPOSE(A2:A)&",", )),,ROWS(B2:B))))&"|", ",|", )}, COUNTA(B2:B), 2)), ", ", ","))