Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

filtering multiple columns to show all colums containing a specific text

I do not know how to explain this however I have been struggling with this all morning. I have an excel sheet and will like to filter the workbook to show me all columns with a specific name. I.e. I can only apply the filter function to one column however I will like excel to show me all columns with a specific name as the name is contained in multiple columns and not just one column. I hope this make sense.
0
Marwan Mohammed
Asked:
Marwan Mohammed
  • 4
  • 3
2 Solutions
 
KoenCommented:
the autofilter works on one column... you will need to use the advanced filter for what you are trying to accomplish...

repeat the headers next to your table and put your 'text' in each of the columns you want to search. However make sure you put only one 'text' in one row (so give that your titels are on row 1, type the first 'text' in row 2 in the column you want to search, the second 'text' in row 3 of another column and so forth). then apply the advanced filter and you should get the desired result.

fyi adding them on the same row works as an AND criterium, putting them on different rows works as OR

see sample for more info
advance-filter.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
Alternatively, add a formula in a spare column to the right of your data to indicate whether the required value occurs on that row. Then filter on that column.

Will the value that you need to find be a complete value and be the complete cell contents? If the search value is "Bob", will the name cells contain only "Bob" or could there be cells with "Bob the Builder" which still need including?
0
 
Rob HensonIT & Database AssistantCommented:
For example, you can use the following formula:

=NOT(ISERROR(FIND($G$1,CONCATENATE(A2,B2,C2),1)))

This will concatenate the contents of columns A B & C (change columns as required) and then look for the contents of G1 in the concatenated text. If the text is found the FIND function will return a number otherwise it will return an error. The ISERROR then converts the result into TRUE or FALSE, True will be an error whereas False will indicate that the text is found; the NOT function then reverses that logic. You can then filter on this column to show only TRUE.

Thanks
Rob
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rob
I think the Countif would be the better choice in this situation as you will have flexibility to expand the range reference used in the formula...

So what about this....
=COUNTIF(A2:C2,"*"&$G$1&"*")>0

Open in new window

And the range is flexible enough to adjust like below
=COUNTIF(A2:AAA2,"*"&$G$1&"*")>0
0
 
Rob HensonIT & Database AssistantCommented:
Indeed, COUNTIF would be better. I had FIND in my head so got blinkered.

Only downside would be if the OP wants to exclude a column in the range where the value could occur but should be ignored, the COUNTIF can only have a contiguous range rather than multiple cells.

Thanks
Rob
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I don't assume any such scenario of skipping columns as per the original post. But it's early to claim so.
0
 
Rob HensonIT & Database AssistantCommented:
I agree it is unlikely!!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Chosen solutions would resolve the question.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now