Multiple inputs from one field into Pivot Table

Question

Dear all,

In my worksheet I have in columns "Ownership" - responsible people for tasks. For example person A, person B, person C, person D and person E but in some cases there are two people responsible for one task. For example person A(alt enter)person B (in one field) or person C(alt enter)person E.

When I am creating pivot table to see the count of task ID for each person, I get in first column "Ownership" followed by rows under it:

Ownership:                ID count:

person A                    (3 tasks)

person B                    (2 tasks)

person C                    (1 task)

person D                    (2 tasks)

person E                     (1 task)

person Aperson B      (1 task)

person Cperson E       (1 task)

This is not my expectation. What I would like to get is that from the field of person A(alt enter)person B pivot table will add +1 count ID to person A, and +1 count ID to person B. Same for the second example. +1 count ID for person C and +1 count ID for person E.

Therefore the result should be as following:

Ownership:                ID count:

person A                    (4 tasks)

person B                    (3 tasks)

person C                    (2 tasks)

person D                    (2 tasks)

person E                     (2 tasks)

Any idea how to do this? Thank you for support

Answer 1

Hi,

Under Insert > Pivot Table, check the box for "Add this data to the data model".  Once you build your Pivot Table, right click on any number in the value area section > Summarise values By > More Options > Distinct Count.

Here's a screenshot

Answer 2

Hello,

ok, but then I will end up with:

Grand total 4. but There is only 3 tasks. not 4.

Answer 3

Hi,

Pivots will not work on datasets with merged and centered cells.  The task ID,description and due date should also appear as many times.

Answer 4

Hello Ashish,

thank you for your answer. I haven't heard about Power Query or Get&Transform before. So I am not sure how to use it just yet. Will try todo some research.

However, I was thinking about the same solution. The problem I am facing is:

Lets say I have a task I have TASK45 (see picture below). which I need to assign to person A and person B. What I do not want to have is the same task in multiple lines. It is not just task ID and then responsible person (ownership) column, there are more columns like, description etc. What I am thinking about is if  create task in two lines but the I Merge each column (two rows into one) apart from Ownership column I will end up with one task for two people (each in separate row) visually.

I tried it (see picture below) but when I create a pivot table, it will only add the task to person in the upper line (therefore person a). also I am afraid that if it would somehow add the count of ID to person B, the grand total of tasks in pivot table will not reflect the current state in the worksheet.

Any thoughts?

Answer 5

Hi,

To get your expected result, each person should appear on a separate row.  This can be done quite easily with Power Query i.e. Get & Transform in Excel 2016.  Do you have that?