Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Code to select ID

    Hi All,

    I'm after some code assistance from the experts.

    In the attached sample spreadsheet in the action tab, I want the code to look at column O (days overdue) and select all over (say) 30, order them into largest to smallest and then extract the corresponding ID number in column A, then copy the ID number into the Summary tab (starting B4) - then the vookups can do the rest.

    I know that this can be done by applying filters and then copying the ID numbers, but I'm trying to make it automated for people that are not really all that excel savvy.

    I hope that makes sense

    Any thoughts or suggestions would be greatly appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    This is definitely a job for Advanced Extract.

    With AE you can extract all fields you need that meet the Days Overdue value and use the Summary sheet as the Extract Range which will automatically copy the values to that range. The only thing you'll have left to do after the extract is to sort.

    Here's VBA to run the Advanced Filter w/Extract option.
    Code:
    Option Explicit
    
    Sub MyExtract()
    
        Range("Database").AdvancedFilter Action:=xlFilterCopy, _
                          CriteriaRange:=Range("Criteria"), _
                          CopyToRange:=Range("Extract"), _
                          Unique:=False
                          
    End Sub
    To set this up you need to do some very SPECIFIC things to your workbook:
    1. All header (column titles) MUST be Identical! I find the easiest way to ensure this is in the Criteria & Extract Ranges to use Absolute Cell References (see attached Sheet).
    2. For best results use a Dynamic Range Name for the Database, see attached sheet.
    3. If at all possible don't leave blank rows in the Extract range as it just confuses things but it will work as you can see in the example.
    4. You only include the columns you want to copy in the Extract Range.
    5. You only need the columns necessary to select the desired records in the Criteria Range but it doesn't hurt to include them all.
    6. Tests placed on the same row of the Criteria Range are AND tests, e.g. all must be true for the record to be selected.
    7. To do OR tests add another row to the criteria range for different records.
    8. If you are only ORing on one or two fields you can include the Column Title more than once.


    Now all you have to do is sort the Summary sheet and cleanup the formatting.

    Test Workbook: Action Test RGV-1.xlsm

    Note: I modified the first couple rows of your data because I didn't scroll down far enough to find ones that actually had overdue amounts.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2016-03-12)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RG,

    Your suggestion looks to work very well. How could the code be amended to clear the existing data (other than the headings) in the summary sheet before populating new data?

    your help of very much appreciated, thanks

    Regards

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi verada

    ..with the Advanced>Filter>Copy to another location vba routine provided by RG, any pre-existing results will be automatically cleared each and every time you run the routine.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2016-03-14)

  7. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Extra criteria problem

    Hi All,

    I'm experimenting with adding additional criteria and additional summary result - please see attached.

    I've tried to amend to Named ranges to reflect the different criteria and extract locations. However for some reason the value in Extract and Extract1 keep going to =Summary!$G$8:$J$8 after I run the macro, even when I change Extract named range to =Summary!$B$8:$E$8 and amended the code to refer to the new named ranges (I think)

    Any thoughts on why this is happening or suggestions on another way of achieving the results?

    Regards
    Attached Files Attached Files

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    That's the weirdest thing I've seen in a long time!

    I fixed it by deleting both Extract & Extract1 via the Name Manager on the Formulas tab.
    I then recreated the ranges using ExtractOne & ExtractTwo and then changing the Code accordingly.

    One other thing when setting up your titles for the Criteria and Extract ranges ALWAYS use FixedReferences referring back to the Database. I noticed that you had your original Extract1 referring back to Extract rather than the Database. I'll apologize for not having fixed references in there originally. I was being lazy and wanted to be able to drag fill when I was setting it up and I forgot to go back and use the F4 key to change them to fixed.

    Here's the revised file: Action Test RGV-2.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    verada (2016-03-16)

  10. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi RG, Thanks for your help again - no need to apologize for anything

    Did I discover some sort of bug or was it just my bad?

    That works just fine. One other question, if I wanted at criteria of say days over due >30 but <100 how would I put that into the days lost criteria?

    Regards

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Easy, just add the Days OverDue column a second time on the same line (and test). Don't forget to redefine the Rangename Criteria1!

    Here's the revised file: Action Test RGV-3.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RG - Got it

    Much appreciated

Posting Permissions

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