Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Using Excel I am searching for records using Filters>Down Arrow>Text Filters>Contains (refer screenshot), this is very tedious as the seach window disappears after taking the imput and I have to repeat the steps again for searching a new word, is there a option where I can input the search word in a neighbouring cell and the list gets filtered?

enter image description here

share|improve this question
up vote 2 down vote accepted

While you could make some VBA-code and/or use the advanced filter, I would recommend you the following solution:

Add another column to your data with this formula pasted down:

M2=IF(ISERROR(SEARCH($X$1,A2,1)),FALSE,TRUE)

With your desired search string inside X1, your search column A with beginning data in row 2 and always starting at the first character.

Now, this will mark your search data, so you can filter by column M = true.

When you change your search data in X1, just update the filter - i.e. press ALT, then R, then S, then E. You can create some shortcut or a makro with custom shortkey to execute just this filterupdate.

share|improve this answer
    
had autocalc on, but this does not refilter your dataset. I use M2 in code to state, that CELL M2 = ... , thanks for the hint with FLASE ^^ - but you know, you could have just suggested an edit for that ;) – Jook Sep 25 '12 at 13:24
    
@Jook sorry for the delay in reply, when you say X1 do you mean cell X1? – Kiranshell Oct 16 '12 at 14:33
    
@Jook Very elegant solution, worked perfectly, thanks Jook! – Kiranshell Oct 16 '12 at 14:45
    
@Jook I know this is a lot to ask, but can the search be done for the full word e.g. searching for "Install" should only find cells containing "Install" and not "Installation". – Kiranshell Oct 16 '12 at 15:20
    
happy to help - you can extend the Formula to i.e. IF(ISERROR(SEARCH($X$1,A2,1),FALSE,EXACT(A2,$X$1)) – Jook Oct 16 '12 at 15:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .