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!
