Results 1 to 6 of 6

Thread: Seeking assistance with criteria based sort and filter.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Seeking assistance with criteria based sort and filter.

    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Seeking assistance with criteria based sort and filter.

    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Feb 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seeking assistance with criteria based sort and filter.

    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/1B4y...ew?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.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Seeking assistance with criteria based sort and filter.

    something like this?



    example
    Last edited by sandy666; Aug 7th, 2019 at 09:11 AM. Reason: link to file added
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Feb 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seeking assistance with criteria based sort and filter.

    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.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Seeking assistance with criteria based sort and filter.

    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)
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •