Counting unique names in the assign to column

Buster Davis

I have the following project sheet. I would do a summary sheet that count the number of unique resources in the assigned to line item/task and then list it out for me.

The comprised data would have the following columns in it.

Assigned Name # of tasks

Jose Garcia 2

Brian Valania 1

Bharath 3

Then i could used the summary data for a chart.

any thoughts on the formula to use. I know I will need to setup a reference

Matt Johnson

Hi @Buster Davis

One option would be to create a report, group it by Assigned to, and summarize (count) by Task Name or something. Or summarize (count) it by status and filter off the completed tasks; giving you an Open Tasks by Team Member report. You could also point the report at multiple project schedules and include the sheet name column on the report. This would give you the Open Tasks by Team Member across the Program or Portfolio all in one report.

I hope that helps.

Matt

Buster Davis

can you share an example of what it would look like.

Paul Newcome

That would normally work, but it becomes much more complex than that because you have multiple people in a single cell.

In a separate sheet, you are going to need 3 columns. All three will be text/number columns. The first one is called "Number". The second is called "Base List", and the third is called "Distinct List".

Number:

Manually enter the number 1 - whatever number you think you will need to accommodate the entire list plus a buffer. So if you think your list is going to be no more than 100 names, then I would suggest going on down to 125.

Base List:

Use the below column formula. You will need to create the cross sheet reference appropriately.

=IFERROR(MID("~" + SUBSTITUTE(JOIN(DISTINCT(COLLECT({Source Sheet Contact Column}, {Source Sheet Contact Column}, @cell <> "")), "~"), ", ", "~") + "~", FIND("!", SUBSTITUTE("~" + SUBSTITUTE(JOIN(DISTINCT(COLLECT({Source Sheet Contact Column}, {Source Sheet Contact Column}, @cell <> "")), "~"), ", ", "~") + "~", "~", "!", Number@row)) + 1, FIND("!", SUBSTITUTE("~" + SUBSTITUTE(JOIN(DISTINCT(COLLECT({Source Sheet Contact Column}, {Source Sheet Contact Column}, @cell <> "")), "~"), ", ", "~") + "~", "~", "!", Number@row + 1)) - (FIND("!", SUBSTITUTE("~" + SUBSTITUTE(JOIN(DISTINCT(COLLECT({Source Sheet Contact Column}, {Source Sheet Contact Column}, @cell <> "")), "~"), ", ", "~") + "~", "~", "!", Number@row)) + 1)), "")

Distinct List:

Use the below column formula.

=IFERROR(INDEX(DISTINCT(COLLECT([Base List]:[Base List], [Base List]:[Base List], @cell <> "")), Number@row), "")

.

From there you can use additional columns to generate whatever metrics you need for your list of users based on the [Distinct List] column.

Buster Davis

Paul for the Distinct list reference, what column would I need to reference in the data setup. Would it be the same as the base list?

Buster Davis

nevermind my last questions

Buster Davis

Paul see below. I was able to used your formulas to produce the following, how would i do the count of the base list?

Paul Newcome

You would do the count on the Distinct List using a COUNTIFS and HAS function.

=COUNTIFS({Source Sheet Contact Column}, HAS(@cell, [Distinct List]@row))

Buster Davis

So I have some progress and a dilema that I need help with.

I am able to create the sheet and get the distint list and total tasks.

Now I would like to figure out how to do a summary sheet that would pulling the above data as I will have multiple sheet that I want to add to the summary report. When I try to create the summary report, I am not getting the options to display those columns Distinct and Total

What am I doing wrong and what would be the correct fix to merge the summary and the base sheet data together?

Paul Newcome

You would need to create this metrics sheet setup for each of the source sheets then create a row report from all of the individual metrics sheets.

Buster Davis

Paul,

so I have the sheet showing the distinct records and that would be my metrics sheet for the one project plan and I would do that for each project plan.

Then you stated I needed to create a row report that would pull these metrics from each sheet that I add to it correct?

Paul Newcome

That is correct.