filtering based on a search result

  • Thread starter Thread starter Yabi
  • Start date Start date
Y

Yabi

Is it possible to filter out few rows based on a search for one word
inside a specific column? If it is not possible, it will be highly
appreciated if you could provide a macro to do this.
In extension of previous question, alternatively, would it be possible
to make that filtering based on search for more than one word.
 
Yabi said:
Is it possible to filter out few rows based
on a search for one word inside a specific column?
Click to expand...

Yes, use Autofilter > custom > contains: <text1>

And if you're filtering up to 2 words,
just check "Or" in the custom autofilter dialog
then > contains: said:
.. alternatively, would it be possible to make that filtering
based on search for more than one word.
Click to expand...

If you have more than 2 words to search, you could set-up a helper col.
Suppose the key col is col A where say: text1, text2, text3, text4 & text5
are to be simultaneously searched in row2 down

In an empty col to the right, say, in col F, list the 5 search text in
F2:F6.

Then place in G2:
=SUMPRODUCT(ISNUMBER(SEARCH($F$2:$F$6,A2))*($F$2:$F$6<>""))>0

Copy G2 down to the last row of data in col A. Col G will return TRUE where
any of the 5 text are found in col A. Just autofilter on col G for TRUE to
get these rows.

Replace SEARCH with FIND if you want to apply a stricter, case sensitive
search for the text. Adapt and extend to suit.

---
 
Dear Max

Thanks again for your valuable informatin like as always. It worked
very well.
However although I am familiar with sumproduct and ISNUMBER command
but couldnt understand how the combination works out.
As a second question, after conducting filter, only for printing
purpose, I want to add a sequential number column to my list however
it doesnt work. Is there any solution for adding a sequential row
number to a filtered list?

Thanks in advance
Rasoul Khoshravan
 
You're welcome, Rasoul.

Suggest you put in a new post for your new query.
(I'm out of ideas on it)
 
Hi Yabi

One way of adding sequential numbers to a filtered list is to copy
the filtered list to a new sheet and add the numbers there.

Not very elegant but it will work.

HTH
Martin
 
You can use a formula to calculate the row number. For example, in
column A, enter the following:
=SUBTOTAL(3,$B$2:B2)

where column B contains no blank cells.
Copy the formula down to the last row of data.

NOTE: Don't use this formula in conjunction with the Data>Subtotal
feature. When you choose to Remove Subtotals, it deletes the lines that
contain a Subtotal formula.
 
Back
Top