ExcelBanter - Excel Filtering

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel Filtering (https://www.excelbanter.com/excel-discussion-misc-queries/67996-excel-filtering.html)

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...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999

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
DataFilterAdvanced 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" wrote:

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...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999

Ron Coderre Wrote:
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
DataFilterAdvanced 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" wrote:

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...o&userid=30924
View this thread:
http://www.excelforum.com/showthread...hreadid=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
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999

:) Ron Coderre Wrote:
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
DataFilterAdvanced 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" wrote:

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...o&userid=30924
View this thread:
http://www.excelforum.com/showthread...hreadid=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
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999

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

------------------------------
"dbusser" wrote:

Ron Coderre Wrote:
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
DataFilterAdvanced 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" wrote:

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...o&userid=30924
View this thread:
http://www.excelforum.com/showthread...hreadid=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
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999

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 wrote:
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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Ron Coderre Wrote:
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

------------------------------
"dbusser" wrote:

Ron Coderre Wrote:
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
DataFilterAdvanced 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" wrote:

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...o&userid=30924
View this thread:
http://www.excelforum.com/showthread...hreadid=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

------------------------------------------------------------------------
dbusser's Profile:
http://www.excelforum.com/member.php...o&userid=30924
View this thread:
http://www.excelforum.com/showthread...hreadid=505999

Thanks Ron

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

Your input has been very helpful and inspiring.

Kind Regards,

Dale

--
dbusser
------------------------------------------------------------------------
dbusser's Profile: http://www.excelforum.com/member.php...o&userid=30924
View this thread: http://www.excelforum.com/showthread...hreadid=505999