# Tricky Pivot Table Analysis

#### soopafly81

##### New Member
Hi everyone! First time poster here after attempting to tackle my issue over several days.

I've included a range of sample data below to help understand what I am trying to achieve.

• I have a list of case numbers (column A) which are duplicated because individuals log separate instances of time to those cases (columns F - I).
• I need my information organized by month which I will use the Date/Time Opened (column C)
• This is where it gets tricky:
• If for the month of December 2020, a person's name appears in the Assigned To field, I need to count the distinct number of case numbers for which they will get credit for working on so I can see the total number of cases worked in a month
• I also need to count the # of minutes from column G for only the time that was created by a specific individual and calculate the total time, independent of whether they were the Assigned To individual or not.
• For example, in the sample data set, Bonnie was assigned to 2 cases, so 2 would be the total.
• However, Bonnie has case time on other cases she wasn't the Assigned To person, which would need to add to her total of Time Spent (Min) which for this data set equals 75.
Is there a different way to filter to capture what I am looking for? Filtering Bonnie on Assigned To excludes all of her other case time spent on other cases.

report1611928294552.xls
ABCDEFGHI
1Case NumberCase OwnerCase Close DateDate/Time OpenedAssigned ToCase Time: Record TypeCase Time: Created ByCase Time: Created DateTime Spent (Min)
200047563Stephanie1/4/2021 7:1412/31/2020 16:37Stephanie02 - Case WorkStephanie1/4/20217
300047558John12/31/2020 16:06John02 - Case WorkBonnie1/27/202110
400047558John12/31/2020 16:06John02 - Case WorkBonnie1/27/202115
500047558John12/31/2020 16:06John02 - Case WorkJohn1/27/202130
600047557Gary1/4/2021 9:4612/31/2020 16:02Gary02 - Case WorkGary1/4/202130
700047556Linda1/6/2021 9:2312/31/2020 16:02Linda02 - Case WorkLinda12/31/20204
800047555Laura1/22/2021 8:3912/31/2020 15:52Laura01 - Case ManagementLaura1/4/202120
900047550Stephanie12/31/2020 15:09Stephanie01 - Case ManagementStephanie1/27/202115
1000047550Stephanie12/31/2020 15:09Stephanie01 - Case ManagementStephanie1/20/202110
1100047549Stephanie1/5/2021 7:0412/31/2020 15:02Stephanie02 - Case WorkStephanie1/4/202110
1200047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy01 - Case ManagementKristy12/31/2020180
1300047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy02 - Case WorkKristy1/5/2021165
1400047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy02 - Case WorkKristy1/5/202125
1500047543Dina1/5/2021 11:1412/31/2020 14:05Dina02 - Case WorkDina1/4/202190
1600047543Dina1/5/2021 11:1412/31/2020 14:05Dina02 - Case WorkDina1/5/202145
1700047543Dina1/5/2021 11:1412/31/2020 14:05Dina01 - Case ManagementDina1/4/202110
1800047538Gary12/31/2020 14:4612/31/2020 12:09Gary02 - Case WorkGary12/31/202045
1900047537Linda1/7/2021 11:0412/31/2020 11:58Linda02 - Case WorkLinda1/4/202130
2000047531Kristy1/4/2021 10:2112/31/2020 11:10Kristy01 - Case ManagementKristy12/31/202010
2100047531Kristy1/4/2021 10:2112/31/2020 11:10Kristy01 - Case ManagementKristy1/4/20213
2200047526Jessica1/22/2021 13:1912/31/2020 10:44Jessica01 - Case ManagementJessica12/31/20205
2300047518Gary1/4/2021 15:3312/31/2020 10:21Gary02 - Case WorkSusan1/4/202145
2400047515Laura1/4/2021 9:0012/31/2020 10:01Laura02 - Case WorkLaura12/31/20208
2500047514Laura1/4/2021 8:4712/31/2020 9:58Laura02 - Case WorkLaura12/31/202015
2600047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth01 - Case ManagementKenneth1/6/202110
2700047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth02 - Case WorkKenneth1/6/2021115
2800047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth02 - Case WorkKenneth1/7/202135
2900047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth01 - Case ManagementKenneth1/7/20215
3000047508Dina12/31/2020 9:0712/31/2020 8:51Dina01 - Case ManagementDina12/31/202010
3100047479John1/25/2021 12:3012/30/2020 20:49Christine02 - Case WorkChristine1/7/202165
3200047478John1/25/2021 12:3112/30/2020 20:48Christine02 - Case WorkChristine1/7/2021110
3300047471John12/31/2020 16:2412/30/2020 19:08John02 - Case WorkJohn12/31/202010
3400047468Dina1/4/2021 10:4312/30/2020 17:48Dina02 - Case WorkDina1/4/20215
3500047468Dina1/4/2021 10:4312/30/2020 17:48Dina01 - Case ManagementDina12/30/2020
3600047466Stephanie1/4/2021 12:5912/30/2020 17:33Stephanie02 - Case WorkStephanie1/4/202115
3700047464Linda1/7/2021 10:5412/30/2020 17:12Linda02 - Case WorkLinda1/4/202130
3800047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/28/202130
3900047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/28/202190
4000047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/19/202175
4100047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/18/202145
4200047461Stephanie12/31/2020 13:0412/30/2020 16:52Stephanie01 - Case ManagementStephanie12/30/20205
4300047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy12/31/20203
4400047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy12/31/202010
4500047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy1/4/20213
4600047458John12/30/2020 16:46John02 - Case WorkBonnie1/27/202115
4700047458John12/30/2020 16:46John03 - Case CheckBonnie1/27/202110
4800047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy12/31/20203
4900047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy12/31/202010
5000047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy1/4/20213
5100047453Laura1/7/2021 9:0712/30/2020 16:12Laura02 - Case WorkLaura1/4/202120
5200047452Dina1/4/2021 11:0112/30/2020 16:12Dina01 - Case ManagementDina12/30/20205
5300047452Dina1/4/2021 11:0112/30/2020 16:12Dina02 - Case WorkDina1/4/202115
5400047449Gary12/30/2020 20:0412/30/2020 16:02Gary02 - Case WorkGary12/30/202020
5500047444Linda1/4/2021 10:3012/30/2020 15:37Linda02 - Case WorkLinda1/4/202130
5600047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/28/202160
5700047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/28/202170
5800047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/18/202145
5900047433Gary12/31/2020 9:5612/30/2020 14:40Gary01 - Case ManagementGary12/31/20205
6000047430Lindsay1/4/2021 11:2212/30/2020 14:26Lindsay02 - Case WorkLindsay12/31/20205
6100047429Dina12/30/2020 20:4912/30/2020 14:26Dina01 - Case ManagementDina12/30/20205
6200047427John1/25/2021 12:3012/30/2020 14:22Christine02 - Case WorkChristine1/22/2021180
6300047427John1/25/2021 12:3012/30/2020 14:22Christine02 - Case WorkChristine1/7/2021110
6400047415Laura1/4/2021 11:4412/30/2020 12:33Laura02 - Case WorkLaura12/31/202030
6500047414Gary12/31/2020 14:4612/30/2020 12:31Gary02 - Case WorkGary12/31/202010
6600047405Linda1/7/2021 16:0712/30/2020 11:31Linda02 - Case WorkLinda1/7/202115
6700047402Gary12/30/2020 19:4812/30/2020 11:18Gary02 - Case WorkGary12/30/202020
6800047399John12/30/2020 13:5412/30/2020 11:09John02 - Case WorkJohn12/30/202030
6900047397Dina1/21/2021 12:3112/30/2020 10:58Dina02 - Case WorkDina1/6/20215
7000047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina12/30/202020
7100047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina1/6/20215
7200047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina1/21/20215
7300047393Bonnie12/31/2020 11:1912/30/2020 10:43Bonnie02 - Case WorkBonnie12/31/202015
7900047382Gary12/30/2020 10:2512/30/2020 9:24Gary02 - Case WorkGary12/30/202015
8000047381John12/30/2020 13:5512/30/2020 9:23John02 - Case WorkJohn12/30/202010
8100047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika01 - Case ManagementStephanie12/30/20207
8200047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/6/2021180
8300047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/6/2021165
8400047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika01 - Case ManagementStephanie1/7/20215
8500047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/4/2021210
8600047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/8/202140
8700047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/1/202175
8800047364Linda12/30/2020 9:2512/30/2020 6:30Bonnie02 - Case WorkBonnie12/30/202010
report1611928294552

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

like this?

#### soopafly81

##### New Member
Sandy, do you mind sharing the pivot table field list? I have something similar but I get a different count of case number. I'm getting a total of 4 using the "Distinct" Count of Case Number.

#### soopafly81

##### New Member
I need to figure out a way for the pivot data to show the number of unique cases = 2, and the total time for those 2 days in the range = 75. If you filter by "Case Time: Created By", it incorrectly identifies the number of cases she was Assigned To.

#### sandy666

##### Banned - Rules violations

1. could you please do not enlarge the pictures?
2. maybe I misunderstood your needs

#### soopafly81

##### New Member
I guess a better way to frame it would be to understand what combination of filters and values would get me to be able to display the below results as TRUE. I cannot switch the filters to display the results separately. I need to be able to show this relationship in the same chart.

1. Between 12/30 and 12/31, the distinct number of cases that Bonnie was Assigned To equals 2. If you filter by her name on that field you can see the result = 2.
2. Between 12/30 and 12/31, the sum of Time Spent (Min) for Bonnie was 75 minutes.

#### sandy666

##### Banned - Rules violations

probably you'll need to combine self-table with Power Query
or use Power Pivot with any measures

#### sandy666

##### Banned - Rules violations
 Case Time: Created Date Assigned To Count Sum 31/12/2020 Bonnie 1 25 30/12/2020 Bonnie 1 50 Total 2 75

Last edited:

#### soopafly81

##### New Member
Do you mind sharing your pivot table settings to show how you got to this result?

Also, is there a way when filtering with a pivot table that you can do the following:
• Filter on Assigned To, OR (not AND)
• Case Time: Created By

#### sandy666

##### Banned - Rules violations
this is Power Query and I must fix, optimise and test a few things yet

Replies
1
Views
53
Replies
11
Views
158
Replies
6
Views
117
Replies
0
Views
90
Replies
3
Views
76

1,130,143
Messages
5,640,384
Members
417,140
Latest member
whiteprose

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back