Google-sheets – Google Sheets querying and sorting data from multiple sheets

google sheets

OK, so I have six main sheets in question…

  1. "Overall Team Assignments"

  2. "Design Team Assignments"

  3. "Mechanical Team Assignments"

  4. "Programming Team Assignments"

  5. "Communications Team Assignments"

  6. "Media Team Assignments"

I want to take data from sheets two through six, from cells B3:D, and insert it into "Overall Team Assignments" in C3:E. I also want to sort it by the dates from column D.

This seems like something that should be fairly simple with QUERY, but everything I try either throws an error or doesn't physically change anything.

Here's the example spreadsheet that's uneditable, just in case something happens to the editable one.

Here's the editable one for anyone that wants one to copy, or a playground to try your hand at.

EDIT: This question was referenced, raising question as to whether or not my question was a duplicate. The difference being that I need to sort the data that is being queried as well (that is, sorted by a certain column within each sheet that is being queried from).

EDIT2: From the reference in the previous edit, this…

={filter(A:A, len(A:A)); filter(B:B, len(B:B)); filter(C:C, len(C:C))}

is how to filter out and stack the results of the cells that I want. The problem being that I don't want one of the columns that seperates the first part of the data that I want from the second part. So if I grab the data in two seperate cells (one for the data before the column that I don't want and one for the data after said column) then I have no way to ensure that there isn't some issue with only some of the cells in each row being filled (on the spreadsheet that is being grabbed from) which would mean that if the formula removed empty cells, it could misalign rows in the transfer from one sheet to another. So I need the formula to not match up things from seperate rows when it cuts out empty space (i.e. it needs to make sure that the rows were not altered from one sheet to another).

EDIT3: OK, now that I've finally had some time to work on this again, I've gotten the chance to try to use FILTER, but it can only grab a single row or column at a time. So that means that the results would be stacked up in each column without any way to sort said columns retroactively (after the data has been updated if something has changed, that is) other than manually.

In short, FILTER won't work for what's being done.

But I did figure out what will work. See my answer below.

Best Answer

To sort a query you simply have to wrap it in SORT.

=SORT(
    QUERY(
        {SheetOne!C3:F;SheetTwo!C3:F;SheetThree!F4:G;SheetFour!F4:G}
    )
    , 1, TRUE)

For example, the above formula takes the data from the sheets inside the query, and then sorts by ascending order from column 1.

This also works well to solve the problem of row splitting, where some rows get split up if you use different queries to pull from rows you intend to keep uniform.

Related Topic