Marwan Mohammed
asked on
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.
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
I don't assume any such scenario of skipping columns as per the original post. But it's early to claim so.
I agree it is unlikely!!
Chosen solutions would resolve the question.
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