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
How about some way to put a number in a column for each of the zip and
filter that number
--
Don Guillett
SalesAid Software
[email protected]
"dbusser" <[email protected]> wrote in
message news:[email protected]...
>
> 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
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" 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 YouOriginally Posted by Ron Coderre
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
filtering formula
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
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
> > 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" 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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks