Solved

filtering multiple columns to show all colums containing a specific text

Posted on 2016-09-20
8
34 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
Technology Partners: 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 31

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 31

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 31

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Outlook Free & Paid Tools
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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