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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies