Advanced Filter - extract data to new sheet

[schnautza]

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?

[daffodil11]

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:

=IF(OR(ISNUMBER(SEARCH($A$1,B3)),ISNUMBER(SEARCH($A$1,D3))),TRUE,FALSE)

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.

[Ron Coderre]

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

visiting team     home team
Team Name          (blank)
(blank)            Team Name

(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?

[schnautza]

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

visiting team     home team
Team Name          (blank)
(blank)            Team Name

(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.

[schnautza]

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

[Ron Coderre]

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.

[schnautza]

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!

[Ron Coderre]

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.

[schnautza]

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

[wildpark]

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.

[FDibbins]

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.