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!

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.
"Suzanne" wrote:

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!

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" wrote in message
...
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!

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!
--
Suzanne


"M Scott" wrote:

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.
"Suzanne" wrote:

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!

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


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" wrote in message
...
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!

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".
--
David Biddulph

"Suzanne" wrote in message
...
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!
--
Suzanne


"M Scott" wrote:

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.
"Suzanne" wrote:

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!

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)
<................show non-blanks
* .................show text and ignore numbers
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" wrote in message
...
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 AutofilterCustomContains 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 DataFilterAdvanced 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" wrote:


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" wrote in message
...
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!

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.
--
Suzanne


"David Biddulph" wrote:

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".
--
David Biddulph

"Suzanne" wrote in message
...
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!
--
Suzanne


"M Scott" wrote:

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.
"Suzanne" wrote:

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!

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

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)
<................show non-blanks
* .................show text and ignore numbers
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" wrote in message
...
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 AutofilterCustomContains 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 DataFilterAdvanced 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" wrote:


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" wrote in message
...
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!

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

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

XL2003, WinXP


"Suzanne" wrote:

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

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)
<................show non-blanks
* .................show text and ignore numbers
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" wrote in message
...
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 AutofilterCustomContains 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 DataFilterAdvanced 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" wrote:


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" wrote in message
...
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!