Pivot Table: count multiple columns

[rmb4466]

I've got a worksheet in which each row represents a specific task. For each task, there will be at least one assignee and perhaps up to four assignees. I'd like to make a pivot table that will count the number of times each assignee has been assigned to a task. Currently, when I add each assignee column to the pivot table, they nest under each other. Is there a way to have them count all together instead of nested?

Current worksheet looks like this:

Task ID	Date	Location	Task	Assignee1	Assignee2	Assignee​ <strike></strike>3	Assignee​ ​4<strike></strike>
1	1 Oct 2018	Kansas City, MO	On-site visit	Jon Dough	Jane Doh
2	13 Oct 2018	Arkansas City, KS	Telephone consult	Rick Row
3	20 Oct 2018	Noel, MO	On-site visit​ <strike></strike>	Jon Dough​ <strike></strike>	<strike></strike>
4	3 Nov 2018	Oark, AR	Research	Jane Doh​ <strike></strike>	<strike></strike>
5	16 Nov 2018	Springfield, MO	On-site visit​ <strike></strike>	Rick Row​ ​ <strike></strike>	Jon Dough​ <strike></strike>	<strike></strike>
6	4 Dec 2018	Bogue, KS	Telephone consult​	Jo Schmoe	Jane Doh​ <strike></strike>	Jon Dough​	Rick Row​ <strike></strike>
7	7 Dec 2018	Miami, OK	Research​ <strike></strike>	Jo Schmoe​ <strike></strike>	<strike></strike>
8	19 Dec 2018	Warsaw, MO	On-site visit​ <strike></strike>	Jane Doh​ <strike></strike>	<strike></strike>
9	28 Dec 2018	Kirksville, MO	On-site visit​ <strike></strike>	Jo Schmoe​ <strike></strike>	Jon Dough​ <strike></strike>	<strike></strike>

<tbody>
</tbody>

I'm trying to make a pivot table that looks like this:

Count of Task	Column Labels
Row Labels	(blank)	Oct	Nov	Dec	Grand Total
.On-site visit		3	2	3	8
..Jane Doh​	<strike></strike>	1		1	2
..Jo Schmoe​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>	<strike></strike>			1	1
..Jon Dough​	<strike></strike>	2	1	1	4
..Rick Row​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>	<strike></strike><strike></strike>		1		1
.Research			1	1	2
..Jane Doh​	<strike></strike>		1		1
..Jo Schmoe​	<strike></strike>			1	1
.Telephone consult​	<strike></strike><strike></strike>	1		4	5
..Jane Doh​	<strike></strike><strike></strike>			1	1
..Jo Schmoe​ <strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>	<strike></strike><strike></strike>			1	1
..Jon Dough​	<strike></strike>			1	1
..Rick Row​	<strike></strike>	1		1	2
Grand Total		4	3	8	15

<tbody>
</tbody>

Thanks,
--Ryan

 

[sandy666]

is that what you want?

Task ID

Date

Location

Task

Assignee1

Assignee2

Assignee 3

Assignee 4

Count of Value

Column Labels

1​

01-Oct-18​

Kansas City, MO

On-site visit

Jon Dough

Jane Doh

Row Labels

October

November

December

Grand Total

2​

13-Oct-18​

Arkansas City, KS

Telephone consult

Rick Row

On-site visit

3​

2​

3​

8​

3​

20-Oct-18​

Noel, MO

On-site visit

Jon Dough

Jane Doh

1​

1​

2​

4​

03-Nov-18​

Oark, AR

Research

Jane Doh

Jo Schmoe

1​

1​

5​

16-Nov-18​

Springfield, MO

On-site visit

Rick Row

Jon Dough

Jon Dough

2​

1​

1​

4​

6​

04-Dec-18​

Bogue, KS

Telephone consult

Jo Schmoe

Jane Doh

Jon Dough

Rick Row

Rick Row

1​

1​

7​

07-Dec-18​

Miami, OK

Research

Jo Schmoe

Research

1​

1​

2​

8​

19-Dec-18​

Warsaw, MO

On-site visit

Jane Doh

Jane Doh

1​

1​

9​

28-Dec-18​

Kirksville, MO

On-site visit

Jo Schmoe

Jon Dough

Jo Schmoe

1​

1​

Telephone consult

1​

4​

5​

Jane Doh

1​

1​

Jo Schmoe

1​

1​

Jon Dough

1​

1​

Rick Row

1​

1​

2​

Grand Total

4​

3​

8​

15​

Done with PowerQuery

M-code

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Month Name" = Table.TransformColumns(Source, {{"Date", each Date.MonthName(_), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Month Name",{"Task ID", "Location"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Task"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source Query - Table1

 

[rmb4466]

It appears so, though it doesn't look like a pivot table as I'm familiar with them (to the limited degree that I am familiar with them). Using your method, does the table incorporate new data automatically? (i.e., if we add a new entry for Rick Row doing a Research visit in January, would it add a new row for Rick Row under Research and a new column for January?)

I'm unfamiliar with PowerQuery...going to have to do some poking around.

Thanks for your response!

 

[sandy666]

You mean like this?

Task ID

Date

Location

Task

Assignee1

Assignee2

Assignee 3

Assignee 4

Count of Value

Column Labels

1​

01-Oct-18​

Kansas City, MO

On-site visit

Jon Dough

Jane Doh

Row Labels

October

November

December

January

Grand Total

2​

13-Oct-18​

Arkansas City, KS

Telephone consult

Rick Row

On-site visit

3​

2​

3​

8​

3​

20-Oct-18​

Noel, MO

On-site visit

Jon Dough

Jane Doh

1​

1​

2​

4​

03-Nov-18​

Oark, AR

Research

Jane Doh

Jo Schmoe

1​

1​

5​

16-Nov-18​

Springfield, MO

On-site visit

Rick Row

Jon Dough

Jon Dough

2​

1​

1​

4​

6​

04-Dec-18​

Bogue, KS

Telephone consult

Jo Schmoe

Jane Doh

Jon Dough

Rick Row

Rick Row

1​

1​

7​

07-Dec-18​

Miami, OK

Research

Jo Schmoe

Research

1​

1​

1​

3​

8​

19-Dec-18​

Warsaw, MO

On-site visit

Jane Doh

Jane Doh

1​

1​

9​

28-Dec-18​

Kirksville, MO

On-site visit

Jo Schmoe

Jon Dough

Jo Schmoe

1​

1​

10​

10-Jan-18​

Any

Research

Rick Row

Rick Row

1​

1​

Telephone consult

1​

4​

5​

Jane Doh

1​

1​

Jo Schmoe

1​

1​

Jon Dough

1​

1​

Rick Row

1​

1​

2​

Grand Total

4​

3​

8​

1​

16​

example excel file

 

[rmb4466]

Looks like it. Now to see if I can replicate what you did...

 

[sandy666]

sure

look at :

• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help

 

[rmb4466]

Great info, thanks!

I think I've pretty much got it set up and reporting properly. One quick question, though: when I make changes to the original dataset, do I always need to refresh the query table and then the pivot table or is there a way to have the automatically refresh?

 

[sandy666]

Both

Refresh PivotTable (you don't need refresh Query Table)
or
You can use any vba code to refresh PivotTable on Worksheet_Change but IMHO it doesn't make any sense with a big data
or
PivotTable option - Data - Refresh data when opening the file

 

[rmb4466]

Got it. Going to have to read your links on Get & Transform and Power Query a bit more closely later.

Thanks for your help!

 

[sandy666]

You are welcome

Power Query aka Get&Transform - it's the same

Have a nice day