Solved

filtering multiple columns to show all colums containing a specific text

Posted on 2016-09-20
8
33 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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 33

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

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
 
LVL 33

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 30

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 33

Expert Comment

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

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

756 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