+ Reply to Thread
Results 1 to 12 of 12

Advanced Filter - extract data to new sheet

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Advanced Filter - extract data to new sheet

    I'm trying to figure out how to use the advanced filter to extract data from a master list to another shorter list.
    This is a master list of results of basketball games. A team can appear in either of two columns (column B and D in my sheet) as a home or visiting team.

    I would like to be able to have Excel scan through columns B and D to find a specific team and return the following columns for all instances of that team onto a separate sheet. I would like for this to automatically update should the master list get longer. It appears that the Advanced Filter just copies the values once and will not update later.

    Column A - date
    Column B - visiting team
    Column C - visiting team score
    Column D - home team
    Column E - home team score
    Column F - Overtime? (OT, 2OT)
    Column G - Neutral Location (if neutral)

    What is the easiest way to accomplish this?
    Last edited by schnautza; 07-24-2013 at 01:48 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Advanced Filter - extract data to new sheet

    The only way I can think to do this would be with an empty cell where you would type in the name of the team you want to see.

    Let's say you insert a row at the top, and put Pink Flamingos in cell A1. (Clearly I'm out of touch with baskbetball team names.)

    Next, we'll use a helper column in column H.

    Assuming your data starts on row 3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and drag it down the rows.

    Now you can just filter by the True(s).

    If you want to get a little more advanced, you could..

    A: Create a Data Validation for Cell A1 that would create a drop down list of Team Names to select from
    B: Create a button linked to a macro that you would click on and it would instantly refresh the True filter for column H to whatever team is in A1.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Advanced Filter - extract data to new sheet

    With your data on Sheet1 in columns A through G, Row_1 contains headings:
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)

    And...On Sheet2
    This criteria list in A1:B3
    Please Login or Register  to view this content.
    (blank) means that cell is blank
    Replace "Team Name" with an actual team name

    And...this Extract list in A6:G6
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)

    • Select Sheet2!A6:G6
    • Data.AdvancedFilter
    ...List Range: (select your data on Sheet1)
    ...Criteria Range: (select A1:B3 on Sheet2)
    ...Check: Copy to another location
    ...Copy to: Select A6:G6
    ...Click: OK

    Notice: you are on Sheet2, but the source data is on Sheet1

    The reason: An advanced filter cannot SEND data to another sheet, but
    it can PULL data from another sheet.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Re: Advanced Filter - extract data to new sheet

    I'm about to add a little more info to the original post in an edit.

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Re: Advanced Filter - extract data to new sheet

    Quote Originally Posted by Ron Coderre View Post
    With your data on Sheet1 in columns A through G, Row_1 contains headings:
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)

    And...On Sheet2
    This criteria list in A1:B3
    Please Login or Register  to view this content.
    (blank) means that cell is blank
    Replace "Team Name" with an actual team name

    And...this Extract list in A6:G6
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)

    • Select Sheet2!A6:G6
    • Data.AdvancedFilter
    ...List Range: (select your data on Sheet1)
    ...Criteria Range: (select A1:B3 on Sheet2)
    ...Check: Copy to another location
    ...Copy to: Select A6:G6
    ...Click: OK

    Notice: you are on Sheet2, but the source data is on Sheet1

    The reason: An advanced filter cannot SEND data to another sheet, but
    it can PULL data from another sheet.

    Is that something you can work with?
    I actually just tried this approach but have one problem - it won't keep up-to-date should the master list change. It appears that the filter just copies the information one time and that is all.

    Really, the output is exactly what I want it to look like - but I need the flexibility so that I don't have to start over every time I want to see a filtered list.

    Ideally, if I could have one control cell at the top of sheet 2 that has a team name, I could change this at will and the list would repopulate for the respective team.

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Advanced Filter - extract data to new sheet

    Here's an approach to try:
    (It's less complicated than it might look)

    Sheet1 in columns A through G, Row_1 contains headings:
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)

    Sheet2 is where you want the extracted data to be displayed

    And...this Extract list in A6:G6
    date
    visiting team
    visiting team score
    home team
    home team score
    Overtime? (OT, 2OT)
    Neutral Location (if neutral)


    Still on Sheet2, cells A1:B3 define the criteria for extraction
    visiting team home team
    Team Name (blank)
    (blank) Team Name

    Note: You can replace Team Name with a formula that refers to the team name you want to extract
    Example:
    C1: a team name
    Now...
    A2: =C1
    B3: =C1

    • Formula.Define_Name.Define_Name
    …Names in workbook: Sheet2!Extract
    …Refers to: =Sheet2!$A$6:$G$6

    • Formula.Define_Name.Define_Name
    …Names in workbook: Sheet2!Criteria
    …Refers to: =Sheet2!$A$1:$B$3

    Still using Sheet2:
    • Formula.Define_Name.Define_Name
    …Names in workbook: Sheet2!Database
    …Refers to: =Sheet1!$A$1:$G$10

    (Notice: you are on Sheet2 and you are creating a Sheet2-level range name
    (by including the sheet name in the Name), but the referenced range
    is on Sheet1)

    The reason:
    An advanced filter cannot SEND data to another sheet,
    but it can PULL data from another sheet.

    Now…set up the Advanced Data Filter:
    • Data.Advanced
    …Check: Copy to another location
    …List Range: (press F3 and select Database)
    …Criteria Range: (press F3 and select Criteria)
    …Copy To: (press F3 and select Extract)
    …Click [OK]

    Note: if you want to run that Advanced Data Filter repeatedly,
    …and…if you're feeling a bit ambitious…

    You can build a simple macro to automatically re-run the filter:
    • Press [Alt]+[F11] to open the VBA editor
    • Right click on the VBA Project folder for your workbook
    …Select: Insert.Module

    Then, copy/paste the below code into that module:

    '---Start of Code-------
    Option Explicit
    Sub PullMatchingData()
    Range("Sheet2!Database").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("Sheet2!Criteria"), _
    CopyToRange:=Range("Sheet2!Extract"), _
    Unique:=False
    End Sub
    '---End of Code-------

    To run the code:
    • Tools.Macro.Macros (or press [Alt]+[F8])
    …Select and run: PullMatchingData

    To test, change the value of C1 and run it again.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Advanced Filter - extract data to new sheet

    schnautza welcome to the forum

    Please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Re: Advanced Filter - extract data to new sheet

    Ron- thank you! This works great.
    Let's see if you could help me with just a couple more tweaks.

    Is there a way to add a button to run the macro instead of going into the ribbon and finding the macro options? (I'm sure there is, I've seen people add buttons all the time, just don't know how.)

    Also, could I get all of the teams in a dropdown list?

    The other tiny problem is that there are just a couple of teams that will grab each other's data (ex: "Lee" and "Lees-McRae" if I search for Lee will pull both)

    After those get fixed, I think you've helped me set up a very user-friendly sheet!

  9. #9
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Advanced Filter - extract data to new sheet

    To add a button...
    • Developer_tab.Insert
    ...Select the Button (form control)
    ...Draw the button where you want it on the worksheet
    ...Select the macro to run when the button is clicked: PullMatchingData
    While it's still selected, name the button
    ...In the Name Box (above Col_A)
    ...Enter: btnRunAdvFltr

    Done...When you click the button, that macro will execute.

    Note: If you don't see the developer tab on the ribbon
    ...File.Options.Customize Ribbon
    ...Choose commands from: Main Tabs
    ...Select: Developer
    ...Click: Add



    For the team list:
    • Put the list in a single-column range of cells
    • Select the cell you will use to choose a team (C1)
    • Data.Data_validation.Data_validation
    ...Allow: List
    ...Source: (select your team list)
    Click: OK

    To enforce an exact match...
    Change the criteria cells to
    A2: ="="&C1
    B3: ="="&C1

    Now, when you run the extraction, only exact matches will be returned.

    I hope that helps.

  10. #10
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Re: Advanced Filter - extract data to new sheet

    BEAUTIFUL.
    This is exactly what I need! Thanks for your help!

  11. #11
    Registered User
    Join Date
    07-23-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Advanced Filter - extract data to new sheet

    Just found this - it's almost exactly what I'm looking for - thank you.

    I did think of an extra wrinkle that might help even more. If you have your database in Sheet1, as Ron suggests above, before you name it, change it to be a table - (click in the database and press CTRL+T). This will give it a name (it usually defaults to Table1, but if you click in the table, go to the Table Tools / Design tab and look in Properties, it will tell you).

    Then, if you use that as the name (so instead of a cell reference, in the name manager it refers to

    =Table1[#All]

    Then if you add or remove data to the bottom of Table1, you don't have to update your name definitions.

    Hope this is helpful.

  12. #12
    Registered User
    Join Date
    07-24-2013
    Location
    Evansville, IN
    MS-Off Ver
    2010
    Posts
    6

    Re: Advanced Filter - extract data to new sheet

    Good idea - what I had done before was define my database range as well beyond what I expect to use, so that was an ok workaround - I like the idea of using a table. Next year, I'll rebuild this sheet again and will consider that alternative.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  2. Second Advanced Filter does not work based on the data generated from the filter
    By mucc1928 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2011, 02:42 PM
  3. Replies: 4
    Last Post: 08-02-2010, 07:16 AM
  4. [SOLVED] Advanced Filter to another sheet
    By Rishi Dhupar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2006, 03:35 PM
  5. [SOLVED] missing or ill egal extract range advanced filter
    By tjtjjtjt in forum Excel General
    Replies: 3
    Last Post: 07-10-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1