Seeking assistance with criteria based sort and filter.

[xoenix]

I have a spreadsheet w/ approximately 40K lines of data. The data is formatted into 3 columns – employee name; week ending; avg. hours worked. Is there a way for me to filter this data in such a way that it omits employees who do not have any hours or entries for the prior two weeks? In the example below, such filtering would only pull Bob Smith because he has hours the week ending 7/28 and the week ending 8/4. However, the filtering would omit Mary Moore because she has no hours these dates. Looking for the most efficient way to sort this large amount of data given the criteria above. Any help you can provide would be greatly appreciated and will have my eternal gratitude! Please note that I cannot attach the spreadsheet here. I do not see that as an option.

Employee Name	Week Ending	Avg. Hours Worked
Bob Smith	07/07/19	8.10
Bob Smith	07/14/19	7.50
Bob Smith	07/21/19	5.50
Bob Smith	07/28/19	6.25
Bob Smith	08/04/19	8.25
Mary Moore	07/07/19	9.00
Mary Moore	07/14/19	8.50
Mary Moore	07/21/19	7.75

<tbody>
</tbody>

 

[sandy666]

but you can post link to the shared excel file with example of source data and expected result, using GoogleDrive, OneDrive, DropBox or any similar

 

[xoenix]

I uploaded an example to google drive. My organization does not allow a one drive shareable link w/ folks outside of the organization. Here's the file.

https://drive.google.com/file/d/1B4yXauRyw_SXS3TTS2XiO0D1HePzRLJs/view?usp=sharing

What I would like to do is sort in such a manner so that it omits employee IDs that do not have entries for the last two week ending dates of 7/20 and 7/27. To be clear, not either or. I only want to omit those employee IDs who have no hours for BOTH of the last week ending dates of 7/20 and 7/27.

 

[sandy666]

something like this?

example

 

[xoenix]

Not quite. I need to omit driver #s that do NOT have entries the past two week ending dates. Filtering as you suggested above would omit every driver # that worked the past two week ending dates.

If employee #1234 worked 6/29, 7/6, 7/13, 7/20, and 7/27 would be included in the filtered list. If employee # 6789 only worked 6/29, 7/6, and 7/13 - but didn't work 7/20 AND (not or) 7/27, they would be excluded from the data.

 

[sandy666]

maybe this one

filtered dates from 27/07/2019 and two weeks back and 20/07/2019 and two weeks back

example

but be aware this is a PowerQuery (Get&Transform)