• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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)
81094

Sheet 2 (Desired Results)
81095
 

Attachments

ETAF

Member
how about

=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

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?

8109981100
 

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.
81106
 

Attachments

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

Top