# How to use a formula to create a comma separated list of values based on a value of a given column

#### MikeB77

##### New Member
Hello. Is there a formula I can use to generate the output below. Basically, for a matching value in column A, generate a list of values in column B.
Thanks for the assistance.

Sheet 1 (List)

Sheet 2 (Desired Results)

#### Attachments

• 12.9 KB Views: 3

#### ETAF

##### Member

=UNIQUE('sheet 1'!\$A\$1:\$A\$12)

=TEXTJOIN(", ", TRUE, IF(A1='sheet 1'!\$A\$1:\$A\$12,'sheet 1'!\$B\$1:\$B\$12, ""))

if you are using latest version of excel 365

#### Attachments

• 14.4 KB Views: 1

#### MikeB77

##### New Member
Thanks, could I use this when additional values are added to columns A and B on sheet 1? e.g. the ability to drag the cursor down to apply to new values added so the new comma separated lists are created?

#### Peter Bartholomew

##### Well-Known Member
This uses more recent array shaping functionality to return the result with a single formula
Code:
``````= LET(
distinct, UNIQUE(group),
members, MAP(distinct,
LAMBDA(d,
TEXTJOIN(", ",,FILTER(item, group=d))
)
),
HSTACK(distinct, members)
)``````
Postscript:
Provided the names are defined so as to have space for the extended data, the correct results should be returned from this version of the formula.

#### Attachments

• 15.7 KB Views: 0
Last edited:

#### ETAF

##### Member
yes
=UNIQUE(FILTER('sheet 1'!\$A\$1:\$A\$100,'sheet 1'!\$A\$1:\$A\$100<>""))
change the rows to max size
or you can just use ciolumn reference - but i dont like doing that
=UNIQUE(FILTER('sheet 1'!\$A:\$A,'sheet 1'!\$A:\$A<>""))

and then
=IF(A1="","",TEXTJOIN(", ", TRUE, IF(A1='sheet 1'!\$A\$1:\$A\$100,'sheet 1'!\$B\$1:\$B\$100, "")))

#### Attachments

• 14.8 KB Views: 1