Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    20
    Thanks
    7
    Thanked 3 Times in 3 Posts

    Macro for pulling data rows

    Hi everyone! This is probably the first post of many to come so here goes:

    Little about myself, I've just got a new job as a data analyst and I don't know how I managed it! I'm okay with the basics of excel however I've been trying to create some pretty complicated stuff and I'm way out of my league right now.

    Basically, we are generating a report for other team members to work off, and at the moment they are applying filters by themselves to get the correct information.

    Each row contains details about one particular job, and one thing to bear in mind is that there are literally thousands of these rows.

    I've used COUNTIFS, VLOOKUP and other basic excel functions to create a summary of "jobs" that are past their deadline, how long past their deadline they are, and who is responsible for that job.

    I want to create macros that when they click on the "summary" cell it will bring up the "jobs" rows that the cell data represents.

    I managed to create a simple macro using the macro recorder that applied the necessary filters on to the data to bring up only jobs that each person was responsible for, however I could not figure out how to apply the COUNTIFS section to only get jobs that had been outstanding for a certain amount of time.

    Also worth noting is that although I could create macros for each person who had jobs, there are over 70 people and over 4 different filter variations so it would need 280 macros which even when copying and pasting the macro in the VBA builder would be very time consuming, and I'm almost certain there must be a better way than this.
    hub 1.jpg
    I've blotted the names out just to be nice as I don't know if these people would like me posting their names and how many jobs they're behind on

    This photo is the current summary section, ideally I'd like it so if you pressed the number in the table it would pull up the jobs that the number represents. I know it must be possible however my limited excel and general programming knowledge is letting me down . I'd vastly appreciate any help.

    If anyone would like to help but needs more information feel free to ask and i'll gladly provide it.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Rathril,

    Welcome to the Lounge as a new poster!

    What would be really helpful would be a sample workbook with test data (just enough different types of records for accurate testing). Of course the names would be changed to things like Donald Duck, etc. Also provide a list of the criteria used in the countifs to get the counts so they can be applied to the raw records. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    Rathril (2014-07-30)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I agree with RG that a copy would be helpful to provide any details. My initial thought is that a pivot table for results with name and the key other filters in the page fieled would coult build the counts (along with other stats) directly without requiring any macros, but I would have to see what you have and understand better what you want/need.

    Steve

  5. #4
    New Lounger
    Join Date
    Jul 2014
    Posts
    20
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hey guys, thanks for your responses!

    I actually have my work laptop with me so I can load a template up, I originally used pivot tables for the count but because there is such a vast amount of data I found that using a long countif with the filter variables provided a much neater summary... that being said I have no doubt you are more proficient than me with pivot tables so I'm definitely open for ideas.

    I just need to find a charger that will fit the laptop so I can boot it up and get it up for you guys asap!

    Thanks again

  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    20
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Okay as promised here is a sample of the sort of sheet i am using

    Almost all the data in the columns is relevant to the people working the overdue jobs, ive only put a few jobs on bearing in mind the actual report has thousands

    Ive highlighted green what columns the filter fields and countifs use, and all the formulas and my current attempt at a macro button are there for you to view as well.
    Attached Files Attached Files

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Rathril,

    Ok here's one possible solution for one possible setup.

    Rather than have a button for each provider I've set it up so you just select the provider name and click a single button (Note: for different criteria you could have similar buttons for each set of criteria).

    This solution uses the Advanced Filter feature of excel to copy the matching records to another sheet where they can be viewed and manipulated w/o affecting the original records.

    I've attached the test sheet (please note I've changed your titles (macro included) to remove the spaces as sometimes these give Advanced Filters problems). I've also remove the question marks from 2 of the items for the same reason but you can replace them in the MFM sheet and see if it makes a difference. Note: If you change any column names always do it ONLY on the MFM sheet as the other Extract sheet uses references to the MFM sheet to make sure the names are identical!

    Here is the macro code used:
    Code:
    Option Explicit
    
    Sub MyList(zProvider As String)
    
    'Note: This code relies on 3 Defined Range Names:
    '      Database:  The raw data records this is currently a dynamic range name with a maximum of 50,000 - 1 records.
    '      Criteria:  The range used to setup the extraction parameters.
    '      Extract:   The location where the selected records are copied.
    
       Dim shtExtract As Worksheet
       
       Set shtExtract = Sheets("Extract")
       
       shtExtract.Activate
        Range("Z2").Value = zProvider
    '    ActiveCell.FormulaR1C1 = "Jonny"
        Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
            ("Criteria"), CopyToRange:=Range("Extract"), Unique:=False
    End Sub
    
    Sub DrillDown_Click()
    
       MyList ActiveCell.Value
    
    End Sub
    Here is the test workbook: sheet test template.xlsm

    Things you'll want to explore:
    1. Range Names Used
    2. Setup for the advanced filter on the Extract sheet.

    If you have any questions please post back.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    New Lounger
    Join Date
    Jul 2014
    Posts
    20
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hello RetiredGeek! I've tried putting that macro into my spreadsheet to no avail yet..
    I'm really entirely new to this level of excel, guess the macro needs slightly editing and i'm not competent enough to do it yet..

    How did you learn how to do the VBA scripting? It might be a good idea for me to go back to basics on this, I feel as if i'm trying to run before I can walk but I'm competent with all the formulas and linking in it's just the macroing functions I need to be able to learn!

    Thanks a lot for your help

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Check out the links/references at: http://eileenslounge.com/viewtopic.p...a0882fbd2c10fe

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    Rathril (2014-07-30)

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Rathril,

    Did you download the workbook on my post? If you did you can just copy your real data into the first sheet starting at row 2 and then the Drill Down button on the Breached Cases sheet should work as long as you have fewer than 50,000 records. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    Rathril (2014-07-30)

  13. #10
    New Lounger
    Join Date
    Jul 2014
    Posts
    20
    Thanks
    7
    Thanked 3 Times in 3 Posts
    I did download your workbook however the one night I don't bring my laptop home is tonight!!

    I will definitely try that tomorrow though, thanks

    The records only really hit 10,000 if we've had a reaaaally bad week so we should be safe on the 50000 limit

    Thanks for the links, I've started checking it out - the main issue I have is that I have now a patchy knowledge from what I've taught myself so I really do think I should just brace myself and start from the beginning! And those should help a lot

Posting Permissions

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