Conditionally concatenate data

New Contributor

I am struggling with creating a concatenated field.  I would like to gather all the people who have status "0" together in a new cell and separate them by commas and sort them alphabetically.  The result should be in a field that is in the record of the one person for each company that has status "1".  There will be a different number of people with status "0" in each company. 

I can't figure out how to make the concatenation conditional on whether the status is "0" or not. Any help you could point me towards would be hugely appreciated.  I'm not a deeply experienced Excel user and have basically used the drop-down menus to create the formulas or copy and pasted formulas I've found online.

I'm attaching a file here which shows the result that I need to accomplish.

Thank you.

2 Replies

Hi @Jane_newbie


It's not possible to sort by surname unless you split the names into 2 separate columns


I'd be tempted to use a Pivot Table to reference the data and then filter just for the 0s,  then apply a formula to join the resulting items.


Do you have the formula =TEXTJOIN available in your version of Excel?



Thanks ... of course I can't sort alphabetically by last name the way the table is. I wasn't thinking. Duh.

I'll look into your suggestions about a pivot table.  I haven't used that so I have some reading to do.  I think I can manage the formula to join the values if I can isolate them.  I'll look into whether I have the TEXTJOIN option as well.  

Thank you for your reply!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies