filtering based on a search result
 

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.

filtering based on a search result
 

"Yabi" wrote
Is it possible to filter out few rows based
on a search for one word inside a specific column?

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: <text2

.. alternatively, would it be possible to make that filtering
based on search for more than one word.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

filtering based on a search result
 

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

filtering based on a search result
 

You're welcome, Rasoul.

Suggest you put in a new post for your new query.
(I'm out of ideas on it)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Yabi" wrote in message
oups.com...
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

filtering based on a search result
 

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

filtering based on a search result
 

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 DataSubtotal
feature. When you choose to Remove Subtotals, it deletes the lines that
contain a Subtotal formula.


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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

filtering based on a search result
 

Dear Debra

It was a very wise solution. Thanks
Rasoul

filtering based on a search result
 

You're welcome. Thanks for letting me know that it helped.

wrote:
Dear Debra

It was a very wise solution. Thanks
Rasoul



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html