How to filter a column of text for exact words

S

Suzanne

Hi,
Can anyone please tell me how to filter a column of text for exact words in
a column which contains over 25,000 descriptions of items. For example, I
would like to see only the ones which contain the exact words TABLE or TABLES
and not other words like TABLET or VEGETABLE, etc. I am using Excel 2003.
I have been searching all day and I'm not proud to say I still can't figure
it out!
 
M

M Scott

Select column heading row and turn on autofilter. On the column choose Custom
Filter from the drop down box.

Value = TABLE
OR
Value = TABLES

If you happen to put TABLE*, TABLET would be included.
 
R

Ron Coderre

Try this:

A5: Words
A6:A25000 contains various words (including multi-word phrases)

A1: Criteria....(or any other word that is not a
column heading in your data OR leave A1 blank)

A2: =MAX(COUNTIF(A6,{"Table","Tables","Table *","Tables *"
,"* Table *","* Tables *","* Table","* Tables"}))

Notice: That formula references the first DATA CELL (A6),
not the column heading (A5).

From the Excel Main Menu:
<data><filter><advanced filter>
....List Range: (select your data, including headings)
....Criteria Range: $A$1:$A$2
....Click [OK]

Now, only rows with Col_A cells that
contain "table" or "tables" will be displayed.
All other data range rows with be hidden.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
S

Suzanne

Thank you! I think that's what I've been doing and it doesn't work for me.
Here is what I did:
Selected the column heading row,
turned on autofilter,
selected Custom from the drop down box,
selected Contains, typed in the word table,
selected Or,
selected Contains, typed in the word tables.
Doing it this way, I get table and tables but I also get tablet, tablets and
vegetables. Can you tell me what am I doing wrong... please!
 
S

Suzanne

Thank you so much for the help. I'm new at this so it took me a while to test
it and I think I did something wrong because it didn't work! Can you please
point out my mistakes.

To be more specific, in my case:
Column H is the column which describes my inventory items. It contains
multi-word phrases.
Column K is the cost, Column R is the asking price and Column S is the price
the item sold for (blank until sold).

Using Autofilter>Custom>Contains in Column H to display most items works
well, I can then autofilter Column S for blanks and see only what I have left
in stock. It is easy to do with Autofilter, but certain words return items I
don't want, table or tables is the first one that came to mind and the ones
that led me to research this.

Another question came to mind: Using Advanced filter, would I need to add
something to the formula to also filter the "sold" column for blanks for
instance, or would I still be able to use Autofilter on the result of the
first Advanced filter??? Mind boggling!

Anyhow, here is what I did:

1. I inserted 4 rows above my headings which had been in Row 1 (my headings
are now in Row 5 and the last row with data is Row 28956.
2. Since in my case, Column H was the one with the multi-word phrases (and
not Column A), in H1, I typed the word "Criteria"
3. In H2, (I changed the A6 to H6 from the formula I was given, I hope I was
right in doing that):
=MAX(COUNTIF(H6,{"Table","Tables","Table *","Tables *","* Table *","* Tables
*","* Table","* Tables"}))
4. H3 and H4 were left blank
5. I then clicked on Data>Filter>Advanced filter
6. I got this message: If you want first row of the selection or list used
as labels & not as data, click Ok. I clicked OK
7. Selected Filter list in place
8. List Range: Sheet1!$H$5:$H$28956
9. Criteria Range: $H$1:$H$2
10. Clicked OK

This is the result:
I get Row 1 to Row 5 and then from 28,957 on (all blank), nothing in between.

I hope you can help me again!
--
Suzanne


Ron Coderre said:
Try this:

A5: Words
A6:A25000 contains various words (including multi-word phrases)

A1: Criteria....(or any other word that is not a
column heading in your data OR leave A1 blank)

A2: =MAX(COUNTIF(A6,{"Table","Tables","Table *","Tables *"
,"* Table *","* Tables *","* Table","* Tables"}))

Notice: That formula references the first DATA CELL (A6),
not the column heading (A5).

From the Excel Main Menu:
<data><filter><advanced filter>
....List Range: (select your data, including headings)
....Criteria Range: $A$1:$A$2
....Click [OK]

Now, only rows with Col_A cells that
contain "table" or "tables" will be displayed.
All other data range rows with be hidden.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Suzanne said:
Hi,
Can anyone please tell me how to filter a column of text for exact words
in
a column which contains over 25,000 descriptions of items. For example, I
would like to see only the ones which contain the exact words TABLE or
TABLES
and not other words like TABLET or VEGETABLE, etc. I am using Excel 2003.
I have been searching all day and I'm not proud to say I still can't
figure
it out!
 
D

David Biddulph

Your mistake was to change the Autofilter condition from "Equals" (which is,
I think, the default) to "Contains".
"Vegetables" contains "table" but it does not equal "table".
 
R

Ron Coderre

Hi, Suzanne

The only way that ALL of the data rows will be
hidden by the Advanced Filter is if:
1) None of the items contains the word "table" or "tables"
or
2) There is a problem with the formula in H2.

I checked the formula you posted and it is correct.
Make sure H2 contains an actualformula. It should display
as 1 or 0...not the contents of the formula.

Also, if you want the SOLD column to also filter:
I1: Sold......(notice: this time we used the column title.)
I2: (you have many options here)
300000.....show SOLD amts greater than 300,000
etc

So, now the Criteria Range would be $H$1:$I$2

To help make things a bit clearer, I posted a
demo file at this link:
http://www.savefile.com/files/1277048

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Suzanne said:
Thank you so much for the help. I'm new at this so it took me a while to
test
it and I think I did something wrong because it didn't work! Can you
please
point out my mistakes.

To be more specific, in my case:
Column H is the column which describes my inventory items. It contains
multi-word phrases.
Column K is the cost, Column R is the asking price and Column S is the
price
the item sold for (blank until sold).

Using Autofilter>Custom>Contains in Column H to display most items works
well, I can then autofilter Column S for blanks and see only what I have
left
in stock. It is easy to do with Autofilter, but certain words return items
I
don't want, table or tables is the first one that came to mind and the
ones
that led me to research this.

Another question came to mind: Using Advanced filter, would I need to add
something to the formula to also filter the "sold" column for blanks for
instance, or would I still be able to use Autofilter on the result of the
first Advanced filter??? Mind boggling!

Anyhow, here is what I did:

1. I inserted 4 rows above my headings which had been in Row 1 (my
headings
are now in Row 5 and the last row with data is Row 28956.
2. Since in my case, Column H was the one with the multi-word phrases (and
not Column A), in H1, I typed the word "Criteria"
3. In H2, (I changed the A6 to H6 from the formula I was given, I hope I
was
right in doing that):
=MAX(COUNTIF(H6,{"Table","Tables","Table *","Tables *","* Table *","*
Tables
*","* Table","* Tables"}))
4. H3 and H4 were left blank
5. I then clicked on Data>Filter>Advanced filter
6. I got this message: If you want first row of the selection or list used
as labels & not as data, click Ok. I clicked OK
7. Selected Filter list in place
8. List Range: Sheet1!$H$5:$H$28956
9. Criteria Range: $H$1:$H$2
10. Clicked OK

This is the result:
I get Row 1 to Row 5 and then from 28,957 on (all blank), nothing in
between.

I hope you can help me again!
--
Suzanne


Ron Coderre said:
Try this:

A5: Words
A6:A25000 contains various words (including multi-word phrases)

A1: Criteria....(or any other word that is not a
column heading in your data OR leave A1 blank)

A2: =MAX(COUNTIF(A6,{"Table","Tables","Table *","Tables *"
,"* Table *","* Tables *","* Table","* Tables"}))

Notice: That formula references the first DATA CELL (A6),
not the column heading (A5).

From the Excel Main Menu:
<data><filter><advanced filter>
....List Range: (select your data, including headings)
....Criteria Range: $A$1:$A$2
....Click [OK]

Now, only rows with Col_A cells that
contain "table" or "tables" will be displayed.
All other data range rows with be hidden.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Suzanne said:
Hi,
Can anyone please tell me how to filter a column of text for exact
words
in
a column which contains over 25,000 descriptions of items. For example,
I
would like to see only the ones which contain the exact words TABLE or
TABLES
and not other words like TABLET or VEGETABLE, etc. I am using Excel
2003.
I have been searching all day and I'm not proud to say I still can't
figure
it out!
 
S

Suzanne

Thank you for the help, but "Equals" only works for the cells that have the
single words "table" or "tables" in them, most of mine have multi-word
phrases.
 
S

Suzanne

Hi again Ron,
Thank you so much for going out of your way to help me. I knew I had to be
doing something wrong and the clue was in the following sentence "Make sure
H2 contains an actualformula. It should display as 1 or 0...not the contents
of the formula."
This was my first time trying out advanced filters... but it definitely
won't be my last. It worked like a charm, no more tablets and veggies!
Happy Holidays,
--
Suzanne


Ron Coderre said:
Hi, Suzanne

The only way that ALL of the data rows will be
hidden by the Advanced Filter is if:
1) None of the items contains the word "table" or "tables"
or
2) There is a problem with the formula in H2.

I checked the formula you posted and it is correct.
Make sure H2 contains an actualformula. It should display
as 1 or 0...not the contents of the formula.

Also, if you want the SOLD column to also filter:
I1: Sold......(notice: this time we used the column title.)
I2: (you have many options here)
300000.....show SOLD amts greater than 300,000
etc

So, now the Criteria Range would be $H$1:$I$2

To help make things a bit clearer, I posted a
demo file at this link:
http://www.savefile.com/files/1277048

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Suzanne said:
Thank you so much for the help. I'm new at this so it took me a while to
test
it and I think I did something wrong because it didn't work! Can you
please
point out my mistakes.

To be more specific, in my case:
Column H is the column which describes my inventory items. It contains
multi-word phrases.
Column K is the cost, Column R is the asking price and Column S is the
price
the item sold for (blank until sold).

Using Autofilter>Custom>Contains in Column H to display most items works
well, I can then autofilter Column S for blanks and see only what I have
left
in stock. It is easy to do with Autofilter, but certain words return items
I
don't want, table or tables is the first one that came to mind and the
ones
that led me to research this.

Another question came to mind: Using Advanced filter, would I need to add
something to the formula to also filter the "sold" column for blanks for
instance, or would I still be able to use Autofilter on the result of the
first Advanced filter??? Mind boggling!

Anyhow, here is what I did:

1. I inserted 4 rows above my headings which had been in Row 1 (my
headings
are now in Row 5 and the last row with data is Row 28956.
2. Since in my case, Column H was the one with the multi-word phrases (and
not Column A), in H1, I typed the word "Criteria"
3. In H2, (I changed the A6 to H6 from the formula I was given, I hope I
was
right in doing that):
=MAX(COUNTIF(H6,{"Table","Tables","Table *","Tables *","* Table *","*
Tables
*","* Table","* Tables"}))
4. H3 and H4 were left blank
5. I then clicked on Data>Filter>Advanced filter
6. I got this message: If you want first row of the selection or list used
as labels & not as data, click Ok. I clicked OK
7. Selected Filter list in place
8. List Range: Sheet1!$H$5:$H$28956
9. Criteria Range: $H$1:$H$2
10. Clicked OK

This is the result:
I get Row 1 to Row 5 and then from 28,957 on (all blank), nothing in
between.

I hope you can help me again!
--
Suzanne


Ron Coderre said:
Try this:

A5: Words
A6:A25000 contains various words (including multi-word phrases)

A1: Criteria....(or any other word that is not a
column heading in your data OR leave A1 blank)

A2: =MAX(COUNTIF(A6,{"Table","Tables","Table *","Tables *"
,"* Table *","* Tables *","* Table","* Tables"}))

Notice: That formula references the first DATA CELL (A6),
not the column heading (A5).

From the Excel Main Menu:
<data><filter><advanced filter>
....List Range: (select your data, including headings)
....Criteria Range: $A$1:$A$2
....Click [OK]

Now, only rows with Col_A cells that
contain "table" or "tables" will be displayed.
All other data range rows with be hidden.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi,
Can anyone please tell me how to filter a column of text for exact
words
in
a column which contains over 25,000 descriptions of items. For example,
I
would like to see only the ones which contain the exact words TABLE or
TABLES
and not other words like TABLET or VEGETABLE, etc. I am using Excel
2003.
I have been searching all day and I'm not proud to say I still can't
figure
it out!
 
R

Ron Coderre

I'm so glad I could help, Suzanne...(and thanks for the feedback)

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

XL2003, WinXP


Suzanne said:
Hi again Ron,
Thank you so much for going out of your way to help me. I knew I had to be
doing something wrong and the clue was in the following sentence "Make sure
H2 contains an actualformula. It should display as 1 or 0...not the contents
of the formula."
This was my first time trying out advanced filters... but it definitely
won't be my last. It worked like a charm, no more tablets and veggies!
Happy Holidays,
--
Suzanne


Ron Coderre said:
Hi, Suzanne

The only way that ALL of the data rows will be
hidden by the Advanced Filter is if:
1) None of the items contains the word "table" or "tables"
or
2) There is a problem with the formula in H2.

I checked the formula you posted and it is correct.
Make sure H2 contains an actualformula. It should display
as 1 or 0...not the contents of the formula.

Also, if you want the SOLD column to also filter:
I1: Sold......(notice: this time we used the column title.)
I2: (you have many options here)
300000.....show SOLD amts greater than 300,000
etc

So, now the Criteria Range would be $H$1:$I$2

To help make things a bit clearer, I posted a
demo file at this link:
http://www.savefile.com/files/1277048

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Suzanne said:
Thank you so much for the help. I'm new at this so it took me a while to
test
it and I think I did something wrong because it didn't work! Can you
please
point out my mistakes.

To be more specific, in my case:
Column H is the column which describes my inventory items. It contains
multi-word phrases.
Column K is the cost, Column R is the asking price and Column S is the
price
the item sold for (blank until sold).

Using Autofilter>Custom>Contains in Column H to display most items works
well, I can then autofilter Column S for blanks and see only what I have
left
in stock. It is easy to do with Autofilter, but certain words return items
I
don't want, table or tables is the first one that came to mind and the
ones
that led me to research this.

Another question came to mind: Using Advanced filter, would I need to add
something to the formula to also filter the "sold" column for blanks for
instance, or would I still be able to use Autofilter on the result of the
first Advanced filter??? Mind boggling!

Anyhow, here is what I did:

1. I inserted 4 rows above my headings which had been in Row 1 (my
headings
are now in Row 5 and the last row with data is Row 28956.
2. Since in my case, Column H was the one with the multi-word phrases (and
not Column A), in H1, I typed the word "Criteria"
3. In H2, (I changed the A6 to H6 from the formula I was given, I hope I
was
right in doing that):
=MAX(COUNTIF(H6,{"Table","Tables","Table *","Tables *","* Table *","*
Tables
*","* Table","* Tables"}))
4. H3 and H4 were left blank
5. I then clicked on Data>Filter>Advanced filter
6. I got this message: If you want first row of the selection or list used
as labels & not as data, click Ok. I clicked OK
7. Selected Filter list in place
8. List Range: Sheet1!$H$5:$H$28956
9. Criteria Range: $H$1:$H$2
10. Clicked OK

This is the result:
I get Row 1 to Row 5 and then from 28,957 on (all blank), nothing in
between.

I hope you can help me again!
--
Suzanne


:


Try this:

A5: Words
A6:A25000 contains various words (including multi-word phrases)

A1: Criteria....(or any other word that is not a
column heading in your data OR leave A1 blank)

A2: =MAX(COUNTIF(A6,{"Table","Tables","Table *","Tables *"
,"* Table *","* Tables *","* Table","* Tables"}))

Notice: That formula references the first DATA CELL (A6),
not the column heading (A5).

From the Excel Main Menu:
<data><filter><advanced filter>
....List Range: (select your data, including headings)
....Criteria Range: $A$1:$A$2
....Click [OK]

Now, only rows with Col_A cells that
contain "table" or "tables" will be displayed.
All other data range rows with be hidden.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi,
Can anyone please tell me how to filter a column of text for exact
words
in
a column which contains over 25,000 descriptions of items. For example,
I
would like to see only the ones which contain the exact words TABLE or
TABLES
and not other words like TABLET or VEGETABLE, etc. I am using Excel
2003.
I have been searching all day and I'm not proud to say I still can't
figure
it out!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top