Excel Filtering

[dbusser]

Hello Everyone

I have an Excel spreadsheet with abut 3,000 lines of sales information
exported from a large sales/contact program called Avenue.

This Excel file includes dropship information to several customers in
California. I would like to be able to sort through this file using a
multiple ( 20) zip codes filter and pull out information on sales to
specific zip codes. As I have found the custom filter only allows me to
use 2 zip codes at a time.

Any help would be greatly appreciated.

Dale

 

[Don Guillett]

How about some way to put a number in a column for each of the zip and
filter that number

 

[Guest]

Try something like this:

Assuming your data is in cells A1:Z3000, with Col_G containing the ZipCode:

Of to the side of your data (or on another sheet), enter the list of
zipcodes you want to find. I'll put my list on Sheet2, cells A1:A20.

On Sheet 1:
AB1: ZipMatch
AB2: =ISNUMBER(MATCH(G2,Sheet2!$A$1:$A$20,0))

Select your data list
Data>Filter>Advanced Filter
List Range: (your already selected data list)
Criteria Range: $AB$1:$AB$2
Click the [OK] button to only display records with addresses in your zipcode
list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[dbusser]

Try something like this:

Assuming your data is in cells A1:Z3000, with Col_G containing the
ZipCode:

Of to the side of your data (or on another sheet), enter the list of
zipcodes you want to find. I'll put my list on Sheet2, cells A1:A20.

On Sheet 1:
AB1: ZipMatch
AB2: =ISNUMBER(MATCH(G2,Sheet2!$A$1:$A$20,0))

Select your data list
Data>Filter>Advanced Filter
List Range: (your already selected data list)
Criteria Range: $AB$1:$AB$2
Click the [OK] button to only display records with addresses in your
zipcode
list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hello Everyone

I have an Excel spreadsheet with abut 3,000 lines of sales information
exported from a large sales/contact program called Avenue.

This Excel file includes dropship information to several customers in
California. I would like to be able to sort through this file using a
multiple ( 20) zip codes filter and pull out information on sales to
specific zip codes. As I have found the custom filter only allows me to
use 2 zip codes at a time.

Any help would be greatly appreciated.

Dale


--
dbusser
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30924
View this thread: http://www.excelforum.com/showthread.php?threadid=505999

Thank You

You are correct, one column is only zip codes. Where do I enter this
formula?
I understand the filter will be created on sheet 2, and I know where
the advacned filter is found. Do I put this formula in a cell or at the
top?
I see these formulas all over the forum but have no experiece using
them.

Thank You for all your help

Dale

 

[dbusser]

Try something like this:

Assuming your data is in cells A1:Z3000, with Col_G containing the
ZipCode:

Of to the side of your data (or on another sheet), enter the list of
zipcodes you want to find. I'll put my list on Sheet2, cells A1:A20.

On Sheet 1:
AB1: ZipMatch
AB2: =ISNUMBER(MATCH(G2,Sheet2!$A$1:$A$20,0))

Select your data list
Data>Filter>Advanced Filter
List Range: (your already selected data list)
Criteria Range: $AB$1:$AB$2
Click the [OK] button to only display records with addresses in your
zipcode
list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hello Everyone

I have an Excel spreadsheet with abut 3,000 lines of sales information
exported from a large sales/contact program called Avenue.

This Excel file includes dropship information to several customers in
California. I would like to be able to sort through this file using a
multiple ( 20) zip codes filter and pull out information on sales to
specific zip codes. As I have found the custom filter only allows me to
use 2 zip codes at a time.

Any help would be greatly appreciated.

Dale


--
dbusser
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30924
View this thread: http://www.excelforum.com/showthread.php?threadid=505999

Thank You

You are correct, one column is only zip codes. Where do I enter this
formula?
I understand the filter will be created on sheet 2, and I know where
the advacned filter is found. Do I put this formula in a cell or at the
top?
I see these formulas all over the forum but have no experiece using
them.

Thank You for all your help

Dale

 

[Guest]

Let's see if I can be more helpfull this time:

The cells that contain the criteria (ZipMatch and the formula) should be on
the same sheet as the sales data list. The list of 20 zipcodes that you are
interested in matching should be on another sheet.

So, if your 3000 rows of sales information are on Sheet1, the list of 20
zipcodes will be on Sheet 2.

Adjust the references to suit your situation. Post back with any other
questions.

Regards,
Ron

XL2002, WinXP-Pro

------------------------------

Try something like this:

Assuming your data is in cells A1:Z3000, with Col_G containing the
ZipCode:

Of to the side of your data (or on another sheet), enter the list of
zipcodes you want to find. I'll put my list on Sheet2, cells A1:A20.

On Sheet 1:
AB1: ZipMatch
AB2: =ISNUMBER(MATCH(G2,Sheet2!$A$1:$A$20,0))

Select your data list
Data>Filter>Advanced Filter
List Range: (your already selected data list)
Criteria Range: $AB$1:$AB$2
Click the [OK] button to only display records with addresses in your
zipcode
list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hello Everyone

I have an Excel spreadsheet with abut 3,000 lines of sales information
exported from a large sales/contact program called Avenue.

This Excel file includes dropship information to several customers in
California. I would like to be able to sort through this file using a
multiple ( 20) zip codes filter and pull out information on sales to
specific zip codes. As I have found the custom filter only allows me to
use 2 zip codes at a time.

Any help would be greatly appreciated.

Dale


--
dbusser
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30924
View this thread: http://www.excelforum.com/showthread.php?threadid=505999

Thank You

You are correct, one column is only zip codes. Where do I enter this
formula?
I understand the filter will be created on sheet 2, and I know where
the advacned filter is found. Do I put this formula in a cell or at the
top?
I see these formulas all over the forum but have no experiece using
them.

Thank You for all your help

Dale

 

[Debra Dalgleish]

Another option is to list the zip codes on the worksheet. Add a column
to the sales information, to check the zip code in that row, then
AutoFilter on that column.

For example, list the zip codes in column L
Add a column to the sales information, with the heading ZipMatch
In the cell below the heading, enter a formula that refers to the zip
code. For example, if the zip code is in column F:

=COUNTIF(L:L,F2)>0

Copy the formula down to the last row of data
Apply an AutoFilter to the table, and filter the ZipMatch column for TRUE.

 

[dbusser]

Let's see if I can be more helpfull this time:

The cells that contain the criteria (ZipMatch and the formula) shoul
be on
the same sheet as the sales data list. The list of 20 zipcodes tha
you are
interested in matching should be on another sheet.

So, if your 3000 rows of sales information are on Sheet1, the list o
20
zipcodes will be on Sheet 2.

Adjust the references to suit your situation. Post back with an
other
questions.

Regards,
Ron

XL2002, WinXP-Pro

------------------------------

Try something like this:

Assuming your data is in cells A1:Z3000, with Col_G containing the
ZipCode:

Of to the side of your data (or on another sheet), enter the lis of
zipcodes you want to find. I'll put my list on Sheet2, cell A1:A20.

On Sheet 1:
AB1: ZipMatch
AB2: =ISNUMBER(MATCH(G2,Sheet2!$A$1:$A$20,0))

Select your data list
Data>Filter>Advanced Filter
List Range: (your already selected data list)
Criteria Range: $AB$1:$AB$2
Click the [OK] button to only display records with addresses i your
zipcode
list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:


Hello Everyone

I have an Excel spreadsheet with abut 3,000 lines of sales
information
exported from a large sales/contact program called Avenue.

This Excel file includes dropship information to severa customers
in
California. I would like to be able to sort through this fil using
a
multiple ( 20) zip codes filter and pull out information on sale to
specific zip codes. As I have found the custom filter only allow me
to
use 2 zip codes at a time.

Any help would be greatly appreciated.

Dale

------------------------------------------------------------------------

dbusser's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30924
View this thread:
http://www.excelforum.com/showthread.php?threadid=505999

Thank You

You are correct, one column is only zip codes. Where do I enter this
formula?
I understand the filter will be created on sheet 2, and I know where
the advacned filter is found. Do I put this formula in a cell or a the
top?
I see these formulas all over the forum but have no experiece using
them.

Thank You for all your help

Dale

Thanks Ron

I inserted a new column "A" in sheet one entered your formula in cel
A2 of sheet one and then copied/dragged that formula to the bottom o
"A" column.
I entered all of my zip codes in Column A sheet 2. The ifnumber formul
returned a true or false on each line of info on sheet 1. I then applie
an autofilter to the zipmatch column "A" sheet1 and selected true. Th
end result was what I as looking for although not by the means you wer
directing me to. I had trouble with the advanced filter option and wa
unable to figure it out. Could you recommend an Excel reference boo
that would help elevate my understanding and application of this typ
of logic?

Your input has been very helpful and inspiring.

Kind Regards,

Dal