Sorting data in Calc that has empty cells that need to remain?

[StainlessB]

I have a worksheet that has ‘paired’ sets of data that is averaged for plotting (to reduce the number of plotted points). This results in some columns have an empty cell every other row. Many of the cells contain formulas, that pull data from columns that do not have empty (skipped) cells

When I try to sort the worksheet, (regardless of which column) Libre becomes unresponsive and I eventually have to force quite.

I have been copy just the columns with the skipped cells to another worksheet and then sorting only those columns is sorts and removes all the empty spaces. This does work, but I have to toggle between worksheets to see all data versus the averaged results.

Any work around? (I am on a Mac)

[Lupp]

Generally an “every-other-row-design” is poorly manageable. To get more detailed help, please edit your question and attach your example document there.

[StainlessB]

here’s a screens shot. each non-text cell contains the value based on RGB- standard and normalized (CIE0Lab* is also used, but not shown). samples are taken from a pair, so there is a left and a right. sometimes theres quite a variation and it is helpful to see which one is causing the largest shift. The paired sets are average (cells with blanks above/below).

Toggling between worksheets opens too much possibility to human error as the values are typically close to each other - in this case, these are all shades of green). ideally, each pair of rows could be anchored to each other…

[Lupp]

Did you read Uploads: Images vs example files / HowTo?
I am not confident that you will get an answer in the sense you want. Pasting an image instead of attaching a sample file may reduce the prospects to a minimum.
If somebody gets interested in the question and may try to help you, they will need to “play” with an example. No really experienced user will use such a design themselves, and then just know a solution.
The only suggestion I can make so far is to create an extra column containing a sequence of numbers increasing by one every second row and another one per group of criteria containing a compound describing the intended order. Use then the compound as the first, and the numbers as the second criterion when sorting.
Also: Don’t tamper with the original data. Direct the output to a dedicated sheet.
See attached example:
disask115172sortingBadlyStructuredData.ods (65.2 KB)

[StainlessB]

spreadsheet example.ods (41.9 KB)
Here’s a working example (with formulas). The pair data is the row with entries in all columns, and the row above.