Tricky Pivot Table Analysis

S

soopafly81

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

bonnie.png




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
7400047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/202160
7500047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7600047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7700047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7800047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele12/30/202030
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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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.

distinct.png
 
Upvote 0
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.
 
Upvote 0
1. could you please do not enlarge the pictures?
2. maybe I misunderstood your needs
fields.png

pt.png
 
Upvote 0
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.
 
Upvote 0
probably you'll need to combine self-table with Power Query
or use Power Pivot with any measures
 
Upvote 0
Case Time: Created DateAssigned ToCountSum
31/12/2020Bonnie125
30/12/2020Bonnie150
Total275
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
56
Fluff
Fluff
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
160
Fluff
Fluff
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
M
  • Solved
Formula Help.
Replies
3
Views
444
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top