Counting unique names in the assign to column
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
Answers
-
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
-
can you share an example of what it would look like.
-
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.
-
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?
-
nevermind my last questions
-
Paul see below. I was able to used your formulas to produce the following, how would i do the count of the base list?
-
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))
-
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?
-
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.
-
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?
-
That is correct.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!