• 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

  • example file.xlsx
    12.9 KB · Views: 3
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

  • example file-ETAF.xlsx
    14.4 KB · Views: 1
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
 
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

  • example file-ETAF.xlsx
    15.7 KB · Views: 0
Last edited:
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

  • pull columns by criteria into 1 cell.xlsx
    14.8 KB · Views: 1
Back
Top