Need to extract data from other sheet based on Name, Date & Time range to avoid duplicates

Hi Experts,

I am currently using Countifs function to extract data from Raw sheet to Task manager sheet based on Name & Date. But doing so, I am getting the duplicate data since at numerous occasions I have same worker working for the same date but at different time slots.
Now, to avoid duplicates & create the productivity dashboard I am looking to extract data with Time range added so that I can get the correct data.
I am attaching both Raw data & Task manager data sample.
Your help will be much appreciated.
Regards,
Varun

Hi All,

I have updated the new excel sheet with the Coutifs function that I am using & have highlighted the duplicate values that I am getting.

1. A sample sheet should consist of 10-20 rows (max) not 3000.

2. Your sheet shows a whole load of error messages in column J, where (I presume) the formula is meant to be... There are links to an external worksheet that we don't have.

3. I can't see anything highlighted. I can't see any manually calculated rexpected results.

Please re-read the yellow banner (top) and post a properly annotated SAMPLE sheet.

J37 to check dupplicate from J38, if there were, zero, else, counting.

Please Login or Register  to view this content.

Drag down (or maybe up)

There's still something wrong with your description:

=IF(COUNTIFS(B$5:B6,B6,C$5:C6,C6,D$5:D6,D6)=COUNTIFS(B:B,B6,C:C,C6,D:D,D6),COUNTIFS(Sheet1!A:A,[@Date],Sheet1!C:C,[@[Workers Name]]),"")

see pink cells on sheet. Activity is not recorded on sheet 1.

Which "Put away Containers". There are two sub-types.

Try this. It seems OK now.

Hi Glenn,

It is ok but I need to get the results using time range as well. Purely because of the reason, I need to produce the productivity chart using these results so that I can see Mr x worked between 11:00 AM to 12 PM at put away containers & qty was 35 & then again worked at Put away containers between 2 PM to 3 PM & qty was 62 then I need to figure out why the productivity was low during first hour.
Thats why I need the time function in there using the Sheet 1 time & comparing it against the Data'21.
Please let me know if I have explained it properly.
Regards,
Varun

Now I am completely confused.

Your "sample" sheet is a mess. There are time values in columns E & F. There is no indication what these represent, as for Yourda Monika NONE of the 90 records in the raw data (11:20 & 11:46) fall inside EITHER of the time windows. Yet you expect 0 as the answer to appear, as if by a miracle, in the second instance (row 46) which might/might not ... as no explanation was given...) run from 14:37 to 17:57.

We want a sample sheet, one that YOU have put a bit of time into thinking about. Simply regurgitating 14,000 rows of raw data with a results sheet that doesn't reflect your data just is simply inadequate.

I am more than happy to continue here, but NOT with this sheet.

Please read the yellow banner (top) again. Here is a suggestion. 20 rows of raw data from no more thna 2 individuals covering anomore than two time periods and a result sheet showing your (manually calculated) expected results.

Provide a sensible, structured, labelled sample and I'll help. Otherwise, I'm out.

Going to Zzzz now. I'll check it over in our am.

Half-way there. (With a little embarassment), I think you have oversimplified the sheet (sorry!!). This works fine for your sample:

=COUNTIFS(Table2[Date],[@Date],Table2[Column3],[@[Workers Name]],Table2[Start Time],">="&MOD([@[Start Time]],1),Table2[Finish Time],"<="&MOD([@[End Time]],1))

However... in your first sheet there seemed to be multiple instances of the "correct" answer. If that is stillt he case, then (ummm) upolad a problem sheet and I'll take another look. However, for what you have currently submitted, this is OK.