advanced filter (to another worksheet) using a LIST OF VALUES

  • Thread starter Thread starter Sandi Gauthier
  • Start date Start date
S

Sandi Gauthier

hi there...my data source contains the following columns and approx. 1500
records:

territory #
district #
company name
meter #
ACCOUNT NUMBER

i have a list of approx. 50 ACCOUNT NUMBERS that i want to look-up and
filter to a new worksheet.

note: the number of records on the data source will vary month to month, so
i will need to create a 'dynamic' named range.

is there a code i could use that will extract only the records which match
the 50 or so account numbers.

appreciate your assistance!
sandi
 
You can use a dynamic range for the criteria range and the data source.
There are instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

On another sheet, or in an empty area of the data source sheet, create
your criteria range. Use a heading cell that matches the Account Number
heading in the data source. In the cells below, enter the account
numbers that should be extracted.

Select the sheet on which you want the extracted records. Then, choose
Data>Filter>Advanced Filter, and copy the results to a new location.
 
Hi Sandi,

It looks like your database is in A to E and you have a separate column of
Account Numbers you want to look up. You could try this. Give your lookup
list a defined name, say 'lister'. Then in F2 put this formula:

=OR(E2=lister)*1

Do a CTRL-SHIFT-ENTER to enter as an array. Move the cursor to the lower
right corner of the cell and double click to fill down. Select a single cell
in the database and select Autofilter. In column F select '1' and the filter
will show the records that match your list. You can copy these to another
sheet.

I didn't understand your need for a dynamic range on the database. If the
amount of records changes, just delete or add to the formula in column F. If
you need it for the lookup list this could be done.

CHORDially,
Art Farrell
 
Back
Top