0

I have a question that seems similar to this one:

Sorting a sheet without losing group order

I want to sort the Rows on the 2nd Tab of the Spreadsheet ("Sorted Sheet"), so that the rows containing TIME (hours of the day) are sorted from earliest to latest (presumably A-Z).

Each TIME row is grouped with the 3 subsequent rows. This data (about 6 groups of 4 rows) needs to stay intact when sorted. All data needs to remain visible.

Is there a better/easier way to approach this task?

Please keep in mind: All the data on "Sorted Sheet" is automatically drawn from the 1st tab, which is a data-entry tab that will have lots of Data Validations and additional data that is pulled in from other sheets. So, it is ideal to to translate (transpose) it to "Sorted Sheet" with minimal changes in formatting. "Sorted Sheet" is only the 2nd (middle) step of this tool I'm making, so it might not make sense yet to criticize the whole endeavor.

Sample Sheet

1
  • Can you please provide how you wish the output to look like? I can't quite make out what you meant by sort.
    – David Tan
    Commented May 25, 2022 at 1:31

1 Answer 1

0

I'm sure the spreadsheet is useful to you in its present form, but in good faith and with your success as our goal; the reason this is so difficult is that you are breaking a rule of spreadsheet use. Each record should occupy one row. All the built-in tools and formulas assume this practice.

There are many ways forward from here; here's a few:

  • Write a formula that extracts a temporary array of every fourth row, sorts it, then outputs repeated sequences of one sorted row and its subsequent three associated rows. Note that this would necessarily be output elsewhere, and all changes would have to be made in to the original. Or the sorted data could be copy-pasted over the original.
  • Make a new column repeating the data you sort by, but with a unique ID appended, to appear next to each TIME and its three associated rows. Then sort by that column.
  • Program an Apps Script four-at-a-time sorter.
  • Redo the layout of your sheet to have everything associated with one TIME on one row.

I'd recommend the last one. If that makes your sheet so wide it is unsuitable for cases where someone needs to glance at it and see all that info for one TIME at once, consider creating a "Report" type sheet with a formula showing the real data, but rearranged.

0

Your Answer

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

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