Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Adv. Filter help! (Excel2000>)

    Hi,
    I have hit a blank here!!!
    I have a list of 30000 records containing course and delegate info. The list is a typical database style that has the record ID, then the course name, then the name of delegate and then other details.
    I need to filter out all delegates that have completed an intermediate level of course, but not attended the advanced course yet.
    Say: John attended the int. course and the adv. course. Adam attended the adv. course only and Rob attended only the int. course. My filter result should only show Rob.
    How do I set this up this adv. filter in Excel again.???
    If required I will send a cross-section of the data.!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adv. Filter help! (Excel2000>)

    Could you post a small sample?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adv. Filter help! (Excel2000>)

    Here is a sample Hans.
    Hope you can figure something out!!!??
    Thanx!
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adv. Filter help! (Excel2000>)

    One method would be :

    Add a column: DelegateID (In this example 1-3 )
    and assign a course level in another column: 1-intro 2- intermediate and 3-Advanced

    Then you need to get a unique list of DelegateIDs. (See RemoveDupes) in another post.
    When Duplicates are found, compare the course level and remove any record that has a level 3

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adv. Filter help! (Excel2000>)

    If I had to do something like this, I would use Access instead of Excel.

    In Excel, you can use a combination of Advanced Filter, formulas and AutoFilter. In the attached version, I did the following:
    - I used Advanced Filter to copy unique participant names to another range.
    - I cut and pasted the unique names to a new worksheet.
    - I created a formulas that indicates whether someone attended the Intermiate course.
    - Same for the Advanced course.
    - I activated AutoFilter for the new table, and set custom criteria:
    * Intermediate must be > 0 (i.e. attended)
    * Advanced must be 0 (i.e. not attended)

    No doubt someone else will come up with a clever, more flexible solution.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adv. Filter help! (Excel2000>)

    Thanx for all the replies...It may not be the most elegant way of doing this, but it gets me what I need!

    PS:
    1. Hans; would you make use of the find unmatched query to do it in access?

    2. Also, how do you specify the criteria range in excel to filter for unique records for the Delegate. I know you tick the Unique Recods Only box, but how do you specify the criteria range. Do I type Delegate and leave the cell below blank, or simply specify a blank cell as the criteria range. I tried an * (as a wildcard), but it did not work. What must I do to filter for unique delegates.? Remember, I want to do this in the genuine list so I can add extra fields to get a new list to add the functions to!!
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adv. Filter help! (Excel2000>)

    1. In Access, one way to do it would be to create a query that selects the participants of the Intermediate course, another query that selects the participants of the Advanced course, and then a Find Unmatched query that uses those two. It is also possible to do it all in one query with subqueries, but that is harder to design and maintain.

    2. You can leave the Criteria box in the Advanced Filter dialog blank if you don't need criteria.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adv. Filter help! (Excel2000>)

    Fisrt time that i'm using this smiley:
    Hans, I can <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> you... <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adv. Filter help! (Excel2000>)

    Rudi,
    If you want to keep this in Excel take a look at the attached file which I adapted from yours.

    It first filters out (hides) rows with participants who have taken interm AND advance courses.
    Then it hides rows for those who have taken the advanced course, leaving only the intermediates

    You may then use further code on the Range("MyRecords") as needed.
    Here, you may need to use Range("MyRecords") .SpecialCells(xlCellsTypeVisible)

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adv. Filter help! (Excel2000>)

    Thanks GoCush.
    Looks pretty niffty and will turn out to be quickly appliable once the code is generalised. Thanks for all the effort to assist me.
    This will also be useful for future reference!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adv. Filter help! (Excel2000>)

    Hi Rudi,
    I think the attached does what you want using an array formula and autofilter (filter for Course Type = 1). Incidentally, you appear to have some people taking the same course twice on the same day?
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adv. Filter help! (Excel2000>)

    Clever! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adv. Filter help! (Excel2000>)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Thanks! I think you could also do it with sumproduct instead of the sum array formula, though with a list that size I don't think it makes much difference.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adv. Filter help! (Excel2000>)

    Hi Rory,
    Thanx 4 your input. This has some other uses that could be extremely handy. i.e. Duplicate bookings... (BTW - the dates were inputed quickly as sample dates; else we would have had a problem <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. Very impressed with that formula. It earns a *star* from my point of view!!!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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