1

I have a very long list of data, the first column of which is the date. I have several date ranges from within that list that I want to pull out and combine in a smaller list so I can analyze the subset of data on another sheet. Currently, I and just using separate filter() for each range. For example:

={filter(range 1 conditions);{filter(range 2 conditions)...}}

Here is an example in a different format as requested below:

Date    Value 2 Value 3 Value 4
10/18/2018  19.8    16.3    12.7
10/19/2018  20.1    16.6    13.1
10/22/2018  20.3    16.9    13.5
10/23/2018  21.0    17.3    13.6
10/24/2018  21.3    17.7    14.1
10/25/2018  21.6    18.1    14.5
10/26/2018  21.9    18.4    15.0
10/29/2018  22.3    18.7    15.1
10/30/2018  22.5    18.9    15.3
10/31/2018  22.6    19.1    15.5
11/1/2018   23.1    19.4    15.8
11/2/2018   23.4    19.7    16.1
11/5/2018   23.5    20.1    16.6
11/6/2018   23.6    20.3    17.0
11/7/2018   23.7    20.5    17.2
11/8/2018   23.7    20.5    17.4
11/9/2018   23.8    20.6    17.4
11/12/2018  24.2    20.5    16.9
11/13/2018  24.4    20.0    15.6
11/14/2018  24.3    19.7    15.0
11/15/2018  24.3    19.5    14.6
11/16/2018  24.0    19.3    14.7
11/19/2018  23.7    19.2    14.7
11/20/2018  23.3    19.0    14.7
11/21/2018  23.3    19.0    14.7
11/23/2018  23.1    19.0    14.9
11/26/2018  22.9    19.0    15.2
11/27/2018  23.0    18.8    14.6
11/28/2018  22.7    18.7    14.7
11/29/2018  22.3    18.6    14.8
11/30/2018  22.1    18.5    14.9
12/3/2018   21.9    18.4    15.0
12/4/2018   21.6    18.3    14.9
12/6/2018   21.7    18.3    14.8
12/7/2018   21.5    18.1    14.7
12/10/2018  21.3    18.0    14.7
12/11/2018  21.0    17.9    14.7
12/12/2018  20.8    17.7    14.7
12/13/2018  20.5    17.6    14.8
12/14/2018  20.2    17.5    14.8
12/17/2018  20.0    17.3    14.7
12/18/2018  19.8    17.1    14.4
12/19/2018  19.5    16.9    14.2
12/20/2018  19.4    16.7    14.0
12/21/2018  19.2    16.3    13.5
12/24/2018  18.9    16.0    13.1
12/26/2018  18.7    15.8    12.8

If my date ranges were: 10/18/2018 - 10/24/2018, 11/1/2018 - 11/7/2018 and 11/23/2018 - 11/27/2018 I could get the following output using the filter functions as described at the top.

Date    Value 2 Value 3 Value 4
10/18/2018  19.8    16.3    12.7
10/19/2018  20.1    16.6    13.1
10/22/2018  20.3    16.9    13.5
10/23/2018  21.0    17.3    13.6
10/24/2018  21.3    17.7    14.1
11/1/2018   23.1    19.4    15.8
11/2/2018   23.4    19.7    16.1
11/5/2018   23.5    20.1    16.6
11/6/2018   23.6    20.3    17.0
11/7/2018   23.7    20.5    17.2
11/23/2018  23.1    19.0    14.9
11/26/2018  22.9    19.0    15.2
11/27/2018  23.0    18.8    14.6

This puts the data in each range in sequence and excludes the date ranges I don't want, but I have to copy and paste and manually edit the formula for each range. Problem is, I have to do this many times on similar sets of data with varying numbers of ranges. That manual effort of copying and pasting and editing the formula gets to be pretty time consuming.

The question is: How can I pull the various ranges into a combined list in a more efficient way? one that saves me the manual effort of what I described above? I have tried to use AND and OR logic in my filter, but I can't keep that from including rows with date ranges between the various ranges that I want.

Thanks for the help!

Edit: Update first pic and some text for clarity.

Edit: Added data input / output example as requested. Updated some text

  • This question's isn't clear (IMHO the included pictures doesn't help). Please add sample input/output data as text not as picture so it will be easier to take it to play. – Rubén Nov 26 at 19:09
  • Updated the example as requested @Rubén – Plot Device Nov 26 at 19:51
0

You could it in various ways. Here are 2 of them.

=FILTER(A1:A, NOT(ISBLANK(A1:A)))

=QUERY(A1:A,"select * where A is not null")

You should read more about the FILTER, ISBLANK, QUERY functions.

  • Thank you for the answer, but that does not solve the issue. It is my fault though, I should have been more clear. I realize now that the picture is a bit misleading. That whitespace in the pic is not empty or blank. They have date values in it as well, but they are values I want to exclude in the sub list. I will update the question to remove confusion if I can edit it. – Plot Device Nov 22 at 12:39
0

To avoid have to edit manually the formulas, instead of using "hardcoded" (typing the date) conditions you could use references to a cell that contains the conditions. I think that it's easier to handle this using QUERY than appending several FILTER functions.

Example

The following formula filter the range A:D where dates in column A are between the date in E1 and date in F1 (note dates in E1 and F1 should be wrote as text using the following format 'YYYY-MM-dd (the apostrophe is to prevent that Google Sheets convert the text value into a date value)

=QUERY(A:D,"select * where (A> date '"&F1&'" and A< date '"&E1&"' and B = "&G1&")")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.