How to get a distinct list of assignees in my project plan

Marc Shecter

Hi —

How can I display a unique list of assignees for each project phase from the phase column, ensuring each name appears only once per phase, even if they are assigned multiple tasks?

Thanks,

Marc

bisaacs

Hey @Marc Shecter,

You can use JOIN/DISTINCT to do that:

=JOIN(DISTINCT(Assignee:Assignee), CHAR(10))

Putting this in a Contact Column doesn't seem to flow the contact info into the cell however. I'm unsure if there's a way to allow for that unfortunately.

Hope this helps!

Marc Shecter

Thanks for the response @bisaacs — Can you tell me more of what how that solution will help me? it's not clear to me.

-Marc

bisaacs

Hey @Marc Shecter,

I guess I might be confused by your original request. I thought you just wanted a distinct list of every person assigned in the assignee column. Can you clarify what you're hoping to achieve?

Marc Shecter

Hi @bisaacs —You pretty much nailed what I want to do. In addition I want to group the assignees by a value in a single selection column called 'phase'. I just don't know how to operationalize what you provided.

bisaacs

Hey @Marc Shecter,

Hmmm so you want the distinct assignees for each specific phase? How do you have your sheet laid out at the moment (and where do you want the list of assignees on the sheet)? Please remember to remove/hide any sensitive data.

That'll help me have a better understanding of how you are wanting this information to be displayed.

Marc Shecter

Hi @bisaacs —

Thanks for continuing to dig into this with me.

My goal is to see (within a report would be nice) something like this:

Assignees Grouped by Phase

• Strategy
  • Person A
  • Person B
  • Person C
• Define
  • Person A
  • Person B
  • Person C
• Plan

In the image below, you can see my project plan. People A, B, and C are assigned to many work items. I need a distinct list of them grouped by phase.

bisaacs

Hey @Marc Shecter,

What about using a Sheet Summary Field, then creating a sheet summary report from those summaries? Create a different summary field for each Phase, then use JOIN/DISTINCT/COLLECT to list out all the contacts in the field. From there use a Sheet Summary Report to return back the summaries?

Here's the formula I used in the sheet summary that seemed to work:

=JOIN(DISTINCT(COLLECT(Assignee:Assignee, Phase:Phase, "Phase 1")), CHAR(10))

Hope this helps!