Solved

filtering multiple columns to show all colums containing a specific text

Posted on 2016-09-20
8
26 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 32

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

929 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

12 Experts available now in Live!

Get 1:1 Help Now