Solved

filtering multiple columns to show all colums containing a specific text

Posted on 2016-09-20
8
24 Views
Last Modified: 2016-10-09
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
Comment
Question by:Marwan Mohammed
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:Koen
ID: 41806235
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41806266
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points (awarded by participants)
ID: 41806286
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41806316
@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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Expert Comment

by:Rob Henson
ID: 41806397
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41806438
I don't assume any such scenario of skipping columns as per the original post. But it's early to claim so.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41806448
I agree it is unlikely!!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41835708
Chosen solutions would resolve the question.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now