4

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?

migrated from superuser.com Jul 31 '17 at 17:19

This question came from our site for computer enthusiasts and power users.

  • 1
    I like query, and will start using it more! Please feel free to make this into an answer. – twalbaum Jul 31 '17 at 23:25
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.

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))
  • +1 for being an accurate answer to my question, but had to select the other one es correct for being the most helpful. – twalbaum Aug 7 '17 at 7:46

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.