Seeking assistance with criteria based sort and filter.

xoenix

New Member
Joined
Feb 17, 2015
Messages
15
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>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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.
 
Upvote 0
something like this?

screenshot-100.png


example
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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