53
votes
How to export a filtered set of rows from a Google Spreadsheet?
Create a new sheet (Sheet2) within the spreadsheet.
Set the A1 cell to be =filter(Sheet1!A:X, Sheet1!A:A>1). (See docs on the filter function.)
You should then be able to save or export Sheet2 as ...
- 872
11
votes
Is there any way to use a formula in a Google Sheets filter view?
Remember to us the cell under the filter heading in the formula, so if your filter is on A3, use A4.
Here is an example for a filter in cell A1, checking if it matches a value in C1
=(A2=$C$1)
for ...
- 191
9
votes
Accepted
Google Sheets Filter Multi Column List With Another List Using A Function
Use a FILTER/MATCH combination. Example on your data below:
=filter(C52:F57; match(C52:C57; I53:I54; 0))
- 1,649
8
votes
Filter multiple values using filter by condition tool in Google Spreadsheet
And for those who are using the "custom formula" option you will need to know that the formula is based off the cell immediately under the filter. So if your have a filter in Cell A1, use A2 in the ...
- 191
7
votes
Why are sheet Filter Views not updating for new rows?
Apparently in Google Sheets, when you turn on the filter (say on the first row) and you don't create a named filter view, that filter on the first row is still set with a range that only goes to the ...
- 71
6
votes
Average of last 7 non-empty non-blank cells in Google Sheets
TL;DR
=AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))
Example
Explanation
First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be ...
- 290
5
votes
How to export a filtered set of rows from a Google Spreadsheet?
The best way to do this is to just select the cells you want to save as CSV, paste it into notepad, then find/replace tab with a comma.
- 151
5
votes
How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?
I have found a workaround that creates the same result, though it may take a little bit more time to set up.
What I do is create a pivot table of the data that comes in from the Google Forms results,...
- 51
4
votes
Filtering a protected column
At this time, on Google Sheets
owner and users with permissions to edit a protected range
are allowed
to filter it.
to edit saved filters.
users without permissions to edit a protected range
...
- 44.6k
4
votes
How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?
Try a Script that fires onFormSubmit. Get and then re-set the same criteria.
var sheet = SpreadsheetApp.getActiveSheet();
var filter = sheet.getFilter();
if (filter) {
var criteria = filter....
- 41
4
votes
Accepted
How can I filter a set of data based on a column containing a given date in sheets
Short answer
Try
=FILTER(
'Output Checker'!A:P,
ROUNDDOWN('Output Checker'!A:A)=A2,
'Output Checker'!L:L <2,
'Output Checker'!M:M ="NO",
'Output Checker'!N:N ="Yes",
'Output Checker'...
- 44.6k
3
votes
Accepted
Increment Cell Reference in Formula by 10
On Locking Ranges using "$"
The first problem I see is the $ sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-...
- 853
3
votes
Is there any way to use a formula in a Google Sheets filter view?
In filter you choose filter by condition, then custom formula is
=(O:O >date(2016,1,1)) + (O:O ="")
The letter is the column letter. The + is use to do multiple conditions.
(OR)
Best practice ...
3
votes
How to export a filtered set of rows from a Google Spreadsheet?
You can make a Pivot table for that, inserting the desired filter. Then just export it as CSV.
3
votes
Google Sheets Filter Multi Column List With Another List Using A Function
@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this:
=filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))
3
votes
Accepted
How do I get rid of the blank line when I use UNIQUE in Google Sheets?
=UNIQUE(FILTER(A2:A, A2:A<>""))
- 26.2k
3
votes
QUERY with Week Number
weeknum is not a valid scalar function so you need to trick it like this:
=ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY({A2:B, WEEKNUM(A2:A)}, "where Col3 = 21")), 999^99, 2)
=FILTER(A2:B, WEEKNUM(A2:A)=21)
- 26.2k
3
votes
Accepted
If values in a range appear in another range, remove from list in another cell
Here's a way to achieve this
=FILTER(B1:B10,
iferror(ArrayFormula(
VLOOKUP(B1:B10,$A$1:$A$10,1,false))
,true))
EDIT : If you have a lot of values this is maybe a better solution :
=FILTER(B1:...
- 1,968
3
votes
Accepted
No query result when one of the columns used in the output never has a value
You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a ...
- 4,980
3
votes
Accepted
How do I exclude a list of values using a filter in Google Sheets?
The + operator corresponds with an or condition. Your test will yield true for any value, because a value is always different from either value1 or value2 or both, when value1 and value2 are not equal....
- 13.2k
2
votes
FILTER(condition OR condition) syntax?
=FILTER(A:F, REGEXMATCH(A:A, "Combat Medic|Universal"))
=QUERY(A:F, " where A matches 'Combat Medic|Universal'", 0)
- 26.2k
2
votes
How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?
Sometimes if I need to figure out a function on Google Sheets, I start recording a macro, make the change that I want to make, and then stop recording the macro. Doing this, I found out that there is ...
- 21
2
votes
Average of last 7 non-empty non-blank cells in Google Sheets
Yet another way:
=AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1))
Explantion:
filter out all numeric values
turn the column upside down (so that last values are above) by ...
- 2,529
2
votes
How to filter everything in one column out of another
I assume you want to get the values in K that are not appearing in L ? If so (and assuming your data starts on row 2), try:
=filter(K2:K, isna(match(K2:K, L2:L,0)))
If you only want to see unique ...
- 2,529
2
votes
How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?
I spent the morning digging around for something similar.
Unfortunately the function call to UpdateFilter() posted by wriddle2 didn't work. The function isn't defined in Apps Script for a Google ...
- 21
2
votes
Query or filter the data where the start time is past 3:00 pm
3 PM is 15h so you can do:
=FILTER(A1:D, HOUR(C1:C) >= 15)
- 26.2k
Only top scored, non community-wiki answers of a minimum length are eligible