How to reorder columns in output of Filter formula

Question

In Google Sheets, I am using the filter formula to obtain a subset of my data, but I would like to reorder the columns of the output.

Here is a sample dataset, and below is the filter formula I'm using.

=FILTER(data!A:F,data!A:A<DATE(2017,7,1),data!A:A>=DATE(2017,6,1))

This gives me the sample of rows, but the columns are in the same order as the original table. Is there a way to reorder them, e.g. have the 2nd column be last and move all others one to the left?

I understand I can filter columns as well as rows, and with that could use a second filter wrapping the first one, but that would mean making a different formula in each column. Any way this can be done in one formula?

Answer 1

A query may be more straightforward:

=query(data!A:F,"select A,C,D,E,F,B where A>=date'2017-06-01' AND A<date'2017-07-01'")

since selection is by letter rather than sheet+column and it is quite easy to include or exclude headers, limit the output, change the order etc all at the same time.

Answer 2

You can reorder the columns by specifying the columns in an array expression directly. In your case, to swap column B after column F, this works as tested:

=FILTER({data!A:A, data!C:F, data!B:B},data!A:A<DATE(2017,7,1),data!A:A>=DATE(2017,6,1))