Forum Discussion
Help with filtering
Hello everyone,
Could you please help me to do a text filter of all of the columns in a spreadsheet at once as opposed to filtering each column one at a time?
I would like to isolate rows in my entire spreadsheet that contain a certain keyword, such as "India" in any of the row's columns. My hope is to see all of the rows where that term is mentioned so I might find and remove any duplicates.
Thanks for your help,
Stephen
3 Replies
As variant you may add helper column to your range which counts the filtered value in the range, and filter the range on this helper column. That's two steps filtering, not straightforward.
For example, the value on which to filter is in A1. Range is from columns A to D. You may add to column E formula like =COUNTIF($A5:$D5,$A$1) for the row 5, and copy on entire range in column E.
To filter, type in A1 any value, "India" and reapply filter in column E on 1.
Much better if you use Tables, above will work more smoothly.
Thank you so much, Sergei! This is the best answer I have received about this problem. Let me ask a followup, please.
What are the steps to set this up if I want to include the entire spreadsheet in my filter/search? The spreadsheet goes from rows 1 - 8,025 and from columns A - L.
Thank you again, my friend. I really appreciate your help.
Stephen
Stephen, you need to reserve some cell for the text on which you will filter the range. Let say that is $O$1.
In M1 enter the formula
=COUNTIF($A1:$L1,$O$1)If first row is for the headers, which is much more preferable, when into M2
=COUNTIF($A2:$L2,$O$1)Keep staying on this cell and in name box type
Enter - column M will be selected
Ctrl+D - formula will be copied in column till end of the range
Select range, apply filter, type something in O1, in column M filter menu uncheck zero.
