 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007filtering 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. > 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.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Dear Max > [quoted text clipped - 9 lines] > Thanks in advance > Rasoul Khoshravan 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. > Dear Max > [quoted text clipped - 9 lines] > Thanks in advance > Rasoul Khoshravan SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html Dear Debra It was a very wise solution. Thanks Rasoul You're welcome. Thanks for letting me know that it helped. > Dear Debra > > It was a very wise solution. Thanks > Rasoul SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.