I am looking for a way to remove multiple rows of data from a dataset, based on what the value is in one of the columns. For example, I would like to pull all data from columns A:Z, based on the values in column H. If column H is "remove" then that whole row should be removed from the dataset.

My current solution to this was to use =FILTER, and to filter other value aside from "remove" in column H. This worked previously when there were 1-2 different things that could be in column H. The issue is, with my new dataset, there are over 100 different variables that can appear in column H, and adding them all to the filter isn't really viable.

Is there a cleaner way to do this instead of creating a new column, and doing an =IF statement to turn the values into a boolean remove/don't remove, then using the =FILTER on on that column instead, and copying the output to remove this extra column on a new sheet?

Ideally, I'd like to do this exclusively within formulas, but do not mind doing it with a script instead if needed.

Thanks in advance!

CodePudding user response：

As Rory already suggested try-

=FILTER(A2:I13,H2:H13<>"REMOVE")