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