Duplicate Values in Pivot Data Source

[Kh3ldar]

Hello,

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.

Morning shift: 08:00 - 17:00

Mid Shift : 16:00 - 01:00

Night Shift : 23:30 - 08:30

I'm using Slicer to select between shifts.

Our tool provides the raw data I need without separating shifts. So for overlap hours (16:00 - 17:00) we get duplicate data resulting in employees appearing in both morning and mid shift in the Pivot Table. What I want is to Excel to ignore the second data set for duplicate entries and not include them in the pivot. Is this possible? Or is there any other way?

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.

Look at the Employee 1 only on Mid and Morning Shift. Employee 1 should only appear in Mid shift. I added the mid shift first and morning second. don't let that confuse you. The raw data source doesn't include shifts and dates. They will be added manually.

[josephteh]

Create another field in your raw data to identify the overlap shifts and filter away this in PivotTable.

[Kh3ldar]

Create another field in your raw data to identify the overlap shifts and filter away this in PivotTable.

Hello,

I appreciate your help. I downloaded the file and tried to tinker with it. However, I can't seem to get the results I want. I'm a beginner at Excel so I couldn't do much tinkering. As far as I can tell the formula needs to yield true if there are duplicate values with same name, date and shift. Then I'll need to filter away true values. Yours included queue as well. is that necessary? Now due to the nature of our work there are 2 queues this results in duplicate names with different values which needs to be displayed separately. What am I doing wrong? This formula yields all false in my original document. I can't paste the formula because it throws me an error, i just deleted the queue part.

I added more data for you to work with to see exactly what I mean.

[josephteh]

You have to use either Filters or Slicer to filter away the Overlap fields. Please see cell A1 & B1 in Mods sheet of the file I attached.

[Kh3ldar]

You have to use either Filters or Slicer to filter away the Overlap fields. Please see cell A1 & B1 in Mods sheet of the file I attached.

I get the concept I used the exact formula you used on this file with a slicer. However, It doesn't yield the results I need. I'll try to rephrase everything.

We have got 3 different shifts.

Morning shift: 08:00 - 17:00

Mid Shift : 16:00 - 01:00

Night Shift : 23:30 - 08:30

As you can see:

08:00 - 08:30 is overlapping with Morning and Night Shift.
16:00 - 17:00 is overlapping with mid and morning shift.
23:30 - 01:00 is overlapping with night and mid shift.

Our tool provides the 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

Date and Shift info are not provided from our tool. We enter those manually. For example, Employee 23 is on morning shift. We extract the data for morning shift. He is in the data. we mark him as morning shift. We then extract the data for mid shift which is 16:00 - 01:00
so he is in the data again because he was working between 16:00 - 17:00. So we mark him as mid shift as well. So he's now listed in both Mid and morning shift because he's marked as both morning and mid. We need to exclude him from mid shift. We need to somehow ignore the second set of data for employee 23 which was extracted for 16:00 - 01:00 mid shift.

Using the exact formula you provided, I select morning and false, calculations get incorrect. I'm cross checking their data. So by the logic of what you are trying to do, Employee 23 shouldn't be listed in the selection of mid and false.

Also,filtering Mid and True excludes Queue 1 from calculation. This shouldn't happen.

That's what I'm trying to do.

Hope that's clear enough now.

[josephteh]

5 lines for Employee 23 on the same date, which line(s) to exclude?

exclusion.png

[josephteh]

Should the Queue be 1, 2, 3, 4 and 5, rather than 2, 1, 1, 1, 2?

[Kh3ldar]

Should the Queue be 1, 2, 3, 4 and 5, rather than 2, 1, 1, 1, 2?

Thank you for trying to help me out mate. I really appreciate it. Employee 23 is on morning shift. But since all shifts overlap he appears in other shifts. In the overlap of night shift 08:00 - 08:30 and in the overlap of mid shift 16:00 - 17:00

So night and mid should be excluded.

We have 2 queues(channels) on which they moderate so it should be only 1 and 2.

[josephteh]

Try this formula:=[@Shift]=INDEX([Shift],MATCH([@Name]&[@Date],[Name]&[Date],0))

Ctrl+Shift+Enter to enter the above array formula.

[Kh3ldar]

[QUOTE=josephteh;

Ctrl+Shift+Enter to enter the above array formula.[/QUOTE]

Sorry for being a noob but CTRL+SHIFT+ENTER brings up nothing. I copied the formula to the first cell then hit the combination and it applied the formula to cells below. However, the formula still doesn't do what I need. It still doesn't identify overlap hours.

[josephteh]

Tell me what exactly is not right.

[josephteh]

I have changed the formula to show "include" or "exclude".

[Kh3ldar]

I have changed the formula to show "include" or "exclude".

I feel like I'm being a pain in the pooper. Okay, let me try again. When I click on morning shift, it should only include the data from 2 to 52. Now we are trying to do that with a duplicate identifier. With your formula, when I filter Morning shift Exclude option gets disabled many of the employees from mid and night shift appear in the table. So the formula needs to identify values below morning shift as duplicate and ignore them. So It needs to take into account only Name,Shift and date. If an employee is identified as morning shift above the below data needs to be excluded. For mid shift It needs to ignore both night and morning shift values. I get that we are trying to do that with a filter but It hasn't been successful so far

[josephteh]

That's exactly what happened - please see the new PivotTable, with the shift inserted.

[Kh3ldar]

That's exactly what happened - please see the new PivotTable, with the shift inserted.

Checked your version. However, it still does not work as intended. Just got another idea, if value for Queue 1 is less than any other value of queue 1 for the same name, mark that as overlap/exclude. So take MAX of Employee 3's queue 1 values and exclude others.

Edit: Someone helped me on tech forums and made the file at the attachment. but I couldn't make heads or tails of it.

Maybe you can.

[josephteh]

Forum rule #3: Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.

Please post the link here. Thank you.

[Kh3ldar]

Forum rule #3: Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.

Please post the link here. Thank you.

Yeah sorry I tried to do that but it throws me an error "You are not allowed to post any kinds of links, images or videos until you post a few times." this also occurs when I try to post formulas.

[josephteh]

You have to tell exactly what is not right.