0

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.

3
  • 1
    Possible duplicate of Concatenate several columns into one in Google Sheets Sep 5, 2016 at 4:02
  • Hopefully you learned how to join the data from several ranges. Include that in your question so the answerers could focus on the sorting part. Sep 6, 2016 at 21:41
  • I've tried to clarify as much as I can, if I need to I can do a short screencapped video of what I'm trying to explain and link it - if what I said in edit2 doesn't make sense. Sep 7, 2016 at 2:56

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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