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.

[LeroyG]

Not a solution, but just to improve a bit the formulas:
Cell O3: =SUM((A2+A3)/2); there is no need of SUM here, just “=(A2+A3)/2”; the same in columns P, Q, S, T and U.
Cell Y: =SUM(S3/255), just use =S3/255; the same in columns Z and AA.

[StainlessB]

Thank you Luup-

I have just been copying the columns with every other row to a separate page and sorting, which collapses (removes) the blanks. I then copied the paired rows of ortiginal data and pasted in the sorted order (time consuming yes),Then another formula ( ∆E) is applied to determine 'boundaries, as this group of data contains 3 color shades, a couple of outliers).

I’ll take a longer look at your siggestion. Thanks for your efforts!

[LeroyG]

You can select the cells with formulas and choose menu Data - Calculate - Formula to Value.
If the values (columns A:M) must be changed, add two rows (2:3) with the formulas, and keep them as formula (do not select them when Formula to Value is applied), so you can fill them down when new data is added.
If do you need to recover the original order, follow the suggestion of @Lupp before you sort the data.

[StainlessB]

Thank you LeroyG

I use the formula to values when copying to a separate page for sorting. Thanks for the other suggestions regarding my misuse of SUM when not needed , that will save a little time.