Google-sheets – Filtering multiple ranges into a single list

formulasgoogle sheetsgoogle-sheets-dates

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

Best Answer

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.