Filter Help..

[G. Michael Paine]

I have a large data base, one of the columns has a number of categories
(if that is what they are called) and I need to filter more than one of
these at the same time.
I can use the drop-down menu for each column, but how do I choose more
that one of those many items?

Michael.

 

[Jim Gordon MVP]

Hi

There are several ways to filter data tables using the Data menu.

Put the selection cursor into the header row then from the menu choose Data

Filter > AutoFilter. Then click on a drop down triangle for the column you

wish to filter and choose "Custom."

For more suggestions just type the word "filter" in Excel's help and do a
search. There are some good examples in there.

-Jim
--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[G. Michael Paine]

Hi

There are several ways to filter data tables using the Data menu.

Put the selection cursor into the header row then from the menu choose Data
wish to filter and choose "Custom."

For more suggestions just type the word "filter" in Excel's help and do a
search. There are some good examples in there.

-Jim

Yes, I know and have used the drop down filter, and I have used custom,
but the question is how to choose more than one item to be filtered.

-M.P.

 

[Bob Greenblatt]

Yes, I know and have used the drop down filter, and I have used custom,
but the question is how to choose more than one item to be filtered.

-M.P.

Look at the Help topics for Advanced filter. You will have to set up
criteria that specify more than one column.

 

[Domenic]

Hi Michael,

You can use Advanced Filter with either the formula approach or the
non-formula approach.

Assuming you want to filter the following table for Categories A and C:

Name Category Amouont
Joe A 5
Jack B 10
Jane C 6
Frank A 8
John A 2

Formula Approach
----------------------

Enter the following formula below a blank cell, let's say A12:

=OR(B2="A",B2="C")

Select your table
Date > Filter > Advanced Filter
Criteria Range: Sheet1!$A$11:$A$12 (includes blank cell)
Click Ok


Non-Formula Approach
---------------------------

Since you want to filter based on Category only, and for both A and C in
this example, then:

A18: Category
A19: A
A20: C

Select your table
Data > Filter > Advanced Filter
Criteria Range: Sheet1!$A$18:$A$20
Click Ok

Hope this helps!

 

[G. Michael Paine]

Look at the Help topics for Advanced filter. You will have to set up
criteria that specify more than one column.

Hi Again,

I seem to be dense on this matter. In spite of various suggestions I
still don't see the solution to my situation. So here is my need.

A data base of over 39,000 lines, with over 30 columns for each line.
The column I need to filter contains over 10 categories spread among the
39,000 lines. I want to filter 3 to 5 of them out.
Custom Autofilter works fine, but has a limit of 2 items only.

To be honest I do not understand the Advance Filter window at all. That
seems to me to work only with numerical items, not e.g., names.

Michael

 

[JE McGimpsey]

A data base of over 39,000 lines, with over 30 columns for each line.
The column I need to filter contains over 10 categories spread among the
39,000 lines. I want to filter 3 to 5 of them out.
Custom Autofilter works fine, but has a limit of 2 items only.

To be honest I do not understand the Advance Filter window at all. That
seems to me to work only with numerical items, not e.g., names.

Advanced Filter works fine with non-numeric data.

One example. Assume your filter column has the name "Names", and that
you want to retain all 30 columns.

On a second sheet, in cell AF1, enter the filter column (e.g., Names).
In AF2:AFn enter the names to retain.

Select AF1:AFn. Choose Data/Filter/Advanced Filter (ignore the "can't
determine header" dialog if it appears). In the Advanced Filter dialog,
Selet the "Copy to another location" radio button. Select the List range
box, then navigate to the sheet with the data and choose all your data.
In the Criteria range, navigate back to your second sheet and select
AF1:AFn. Select the Copy to box and choose cell A1 of your second sheet.
Click OK.

It actually takes only a few seconds to do this.

You can find additional help at Debra Dalgleish's site:


http://www.contextures.com/xladvfilter01.html

 

[G. Michael Paine]

Advanced Filter works fine with non-numeric data.

One example. Assume your filter column has the name "Names", and that
you want to retain all 30 columns.

On a second sheet, in cell AF1, enter the filter column (e.g., Names).
In AF2:AFn enter the names to retain.

Select AF1:AFn. Choose Data/Filter/Advanced Filter (ignore the "can't
determine header" dialog if it appears). In the Advanced Filter dialog,
Selet the "Copy to another location" radio button. Select the List range
box, then navigate to the sheet with the data and choose all your data.
In the Criteria range, navigate back to your second sheet and select
AF1:AFn. Select the Copy to box and choose cell A1 of your second sheet.
Click OK.

It actually takes only a few seconds to do this.

You can find additional help at Debra Dalgleish's site:


http://www.contextures.com/xladvfilter01.html

Nope, I afraid I did not get across want I want to do.
I don't want to retain columns...I want to filter a number of items in
the same column, but not all of them.

Michael

 

[JE McGimpsey]

Nope, I afraid I did not get across want I want to do.
I don't want to retain columns...I want to filter a number of items in
the same column, but not all of them.

So you just want the values from that column??

 

[G. Michael Paine]

So you just want the values from that column??

I'm not sure 'values' is the correct name, but assuming it is. One of
the aspects of the 39,000 lines is in this column, there are 10 'values'
in that column want to filter out more than two of those 'values'
contained in that column.

 

[JE McGimpsey]

So you just want the values from that column??

I'm not sure 'values' is the correct name, but assuming it is. One of
the aspects of the 39,000 lines is in this column, there are 10 'values'
in that column want to filter out more than two of those 'values'
contained in that column.[/QUOTE]

I guess I'm just not visualizing what your end point is...

Perhaps someone else can jump in?

 

[Domenic]

I'm not sure 'values' is the correct name, but assuming it is. One of
the aspects of the 39,000 lines is in this column, there are 10 'values'
in that column want to filter out more than two of those 'values'
contained in that column.

I guess I'm just not visualizing what your end point is...

Perhaps someone else can jump in?[/QUOTE]

It might be helpful if Michael provided some sample data along with a
sample of the expected result.

 

[Bob Greenblatt]

I'm not sure 'values' is the correct name, but assuming it is. One of
the aspects of the 39,000 lines is in this column, there are 10 'values'
in that column want to filter out more than two of those 'values'
contained in that column.

I guess I'm just not visualizing what your end point is...

Perhaps someone else can jump in?[/QUOTE]
OK, let me try.


Somewhere on the sheet in a spare column, usually better "above" the start
of your database, place the column header of the field you wish to filter
on. Immediately below this, add each value you wish to look at in a separate
cell. Then highlight the database, select filter-advanced filter. The
database range should already be correctly listed. In the criteria range,
highlight the cells you just entered including the column header. When you
click OK, the list should be filtered to show you just the values you have
entered in the criteria.