Pivot Table and Need to Ignore Second set of Data which is duplicate

Kheldar

New Member
Joined
Aug 31, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,


I know it's against rules to cross post but I haven't been able to find a solution to my problem anywhere else. So I'm posting it here again. Please don't delete it :)


I'm trying to achieve something seems really difficult to, I asked about on some other forums including Microsoft tech community but no one seems to be able help me.

Okay,

Here's what I'm trying to do:

I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance during those 9 hours of shifts. They are content moderators and their performance is assessed by how many cases they complete. We have 2 different channels. So they appear twice in the raw data. That's no problem because using Pivot Table, I can filter and do calculations accordingly.


Morning shift: 08:00 - 17:00

Mid Shift : 16:00 - 01:00

Night Shift : 23:30 - 08:30


I'm using Slicer to switch between shifts.

Our tool provides the raw data without distinguishing shifts. So for example, In order to extract all shifts of 23.08, I select the time 08:00 - 17:00 on our tool for morning shift. I then have to extract the data for mid shift which is 16:00 - 01:00 then for night shift 23.09 23:30 24.09 08:30. We mark the first set of data as Morning, then the second is mid shift. Now the problem is since morning shift is between 08:00 - 17:00 and mid 16:00 - 01:00 for overlap hour of 16:00 - 17:00 we get duplicate data and then we mark them again as mid shift which isn't correct. So they appear in both morning shift and mid shift. and since we get duplicate results for the overlap hour the calculation gets incorrect. I want to filter away that second set of data or make Excel ignore it somehow.



Someone on forums suggested me a formula. I left it exactly how it was suggested. By his logic, I need to filter away overlap hours with a true or false logic but It's not working as intended.



Am I clear enough? If not I can provide more info.





I'm attaching the excel file I'm working on for you to see what I'm doing.



 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,081
Office Version
  1. 365
Platform
  1. Windows
I know it's against rules to cross post
It is not against the rules to cross post, but you have to supply link(s) to your question on other sites. Please do so, thanks.
 

Forum statistics

Threads
1,148,421
Messages
5,746,581
Members
424,032
Latest member
pochie2741

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
Top