Collate Data in Google Sheets

Question

I am trying to collate date in Google Sheets. I have multiple tabs and want to consolidate them into one tab where each row is from a different tab. If that doesn't make sense, here is my test spreadsheet.
Tab 1:
Green1     GreenCol1     GreenTest1
Green2     GreenCol2     GreenTest2

Tab 2:
Yellow1    YellowCol1    YellowTest1
Yellow2    YellowCol2    YellowTest2

And so on for however many tabs. What I want to output on the "Master Tab" would be something like this
Master Tab:
Green1     GreenCol1    GreenTest1
Yellow1    YellowCol1   YellowTest1
Green2     GreenCol2    GreenTest2
Yellow2    YellowCol2   YellowTest2

I have the following code currently in A1 of my master tab, but it returns all of the green then all of the yellow, which is what I am trying to avoid.
=QUERY({Sheet2!A1:C;Sheet3!A1:C},"select * where Col1 is not null",0)

Erik Tyler · Answer

Hunter, it's always tough to write formulas without access to an actual spreadsheet and data. Even with what you shared here, there are a lot of unknowns. So you'll always get the fastest and most accurate solutions if you share a link to a sample spreadsheet, being sure to set permission to "Anyone with the link can edit."
That said, based on the limited data you've provided here, see if the following formula does what you had hoped:
=ArrayFormula(QUERY({Sheet2!A1:C,ROW(Sheet2!A1:C);Sheet3!A1:C,ROW(Sheet3!A1:C)},"Select Col1, Col2, Col3 Where Col1 Is Not Null Order By Col4 Asc",0))

What I did was follow each data set in the internal array with a virtual column containing the row numbers of that data set, which I then used as the Order By Column (even though it's not visible in the results).

Collate Data in Google Sheets

One Answer

Add your own answers!

Ask a Question