CODEWITHSUNDEEP
google-sheets
Mark Schmit
Mark Schmit

Reputation: 477

Import/merge data from multiple sheets and then re-sort it intermixedly?

Is it possible to have a Google Sheet that imports all data from multiple sheets but which can then be sorted in an intermixed way in the combined sheet?

I'm creating a spreadsheet for tracking the performance of a bunch of runners across multiple track meets. I've got one sheet for each track meet, and I want to make one combined sheet that has all of the records but that I can sort by whatever parameter I want; e.g. by runner name, by time, by overall performance rank.

Is this possible? I tried writing an =QUERY command like this (where the individual sheets are named after years, i.e. '2022' or '2021'):

=QUERY({'2022'!B1:M;'2021'!B2:M},"Select * where Col2 is not null ORDER BY Col10 asc, Col11 asc")

Even with the ORDER BY directive the QUERY output still has the sheet '2022' rows before '2021' rows. Sorting by another column doesn't change that.

Is there something that lets you merge multiple sheets in a way that lets the records intermingle?

Or is the only way for me to make this work to instead have one "master" sheet and then make the individual meet sheets just be a filter on that? I was hoping to have individual sheets be the source-of-truth :-/.

Upvotes: 0

Views: 260

Answers (1)

doubleunary
doubleunary

Reputation: 18733

Your formula will sort the data first by column K and then by column L, because ColX type identifiers are numbered starting at the first data column. Col1 will refer to column B and Col10 will refer to column K rather than column J as you may expect.

Note that you have left the headers parameter unspecified, which means that query() will try and deduce the number of header rows automatically, which sometimes causes trouble.

To sort by columns J and K instead, explicitly specifying the number of header row as 1, use this:

=query( { '2022'!B1:M; '2021'!B2:M }, "where Col2 is not null order by Col9, Col10", 1 )

Alternatively, keep all the data in the same sheet and use filter views to decide which rows to show at a time. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

Upvotes: 1

Related Questions