Hi,
Is it possible to sort in Calc, using a row as the main sort, but keeping the row below with the one above after the sort? (As in the pic, keeping the Composer with the song name)
Thanks.
Yes, there is a way. You will need an additional auxiliary column, which you will fill in with values using a simple formula. These values will be 1-1-2-2-3-3 for your example. Then convert the formulas to values and sort the list by two columns at once - by the song title and by the column with numbers. The auxiliary column can then be deleted. I do not show the formula that needs to be written - after all, neither the column names nor the line numbers are visible on your screenshot.
Seem that this won’t work.
Maybe the auxiliary column must containt the song name.
EDIT: And a second column with 1 to n to prevent chaos with same-name songs. Then sort by song name and number.
EDIT: Added quote to show what won’t work. Sorry to disturb.
1 Like
Very fundamental:
Insisting on the Every-Other-Row-Design, you are at risk to get problems again and again - not only with sorting.
Consider to redesign your sheet(s) regarding “OneRowOneItem”. In cases like the exemplified one, sooner or later may come the wish to migrate to a database. OROI is essential then.
Also: Please attach your examples as small ODF-files (here .ods) wherever possible. This can allow a contributor to explain his (f/m) suggested solution by a working example.
1 Like
(The good idea by @JohnSun was explained introducing a flaw.)
Any associated pair of rows needs to get the same key, but it must be a key wich can exclusively be used when sorting then…
The rectified solution is demonstrated in the attached example which also should demonstrate the reasons for what I judge it bad style to post images in such cases in place of the needed realistic example. An explanation of a correct solution to a supposed beginner by words and a formula only may soon be too difficult for the experienced guy. He (f/m) may prefer a working example h??self, too. In this case it isn’t fair to expect him to create the example first.
The attached example relies on the assertion that no title can occur a second time. If this doesn’t hold, an obvious little change will remedy.
disask70780SortEveryOtherRowSample_NEVER_DO.ods (19.0 KB)
2 Likes
Looks like a solution!
Pity there isn’t an easier way to accomplish…
Thanks.
There is an easier way - don’t make it hard for yourself by splitting related data across different rows. In this case, you do not have to look for additional options for sorting or filtering. And if the point is only that one text should be bold large and above the rest of the text, then this can be easily achieved using formatting
2 Likes
Thanks!
Yep, that would work, too.
It wouldn’t just also work. It’s simply the better way to do it.
If you want to see a way how to clean up an
Every-Other-Row-Design, and to get a
One-Row-One-Item sheet from it, you may look into the reworked attachment. There are different (and probably better) ways. However: Do it better sooner than later.
disask70780SortEveryOtherRowSample_CleanUp.ods (80.2 KB)
Yes, of course. But…
There is an excuse for users thinking the every-other-rows-way. Most of them will see again and again the idiotic way Windows Explorer is presenting search results by default.
“If it’s the way MS does it, I also should do it this way”?
Do MS actually want to have uninformed and mislead users?
May be. They suppress/hide even most important information by default in the Explorer again.
But:
Why does LibreOffice hide the third (and forth) digit of the version number in its frame titles?
Why isn’t even an option to show the folder path of the workfile there?
Why does LibO set YY-MM-DD as the default date format even for Esperanto “locale”?
The only plausible answer seems to be that we all are crazy.
If you are going to remove the two-line format anyway, might I recommend just adding a blank row below the headers but above the data columns, then highlighting the two cells above the composer and key columns, right-clicking, selecting Delete… and then selecting Shift cells up? Then sort per wishes. Sort will eliminate the blank lines (effectively). This seems easier than messing with math inside the INDEX function.
To go the other way, btw, you can take your data that is in single rows, copy it (without headers), and paste the copy right below the original. Then sort on all your columns. Now you have a perfect stuttered list. Then use something like
=IF(MOD(ROW(),2),"",A2) | =IF(NOT(MOD(ROW(),2)),"",B2) | =IF(NOT(MOD(ROW(),2)),"",B2)
to the side of the first row of data and copy that down to beside the last row of data. The two-line formal will get “carved out” of the duplicated data.
OK, but the issue with that is, with a 2-line config, text can overlap. ie
Thanks.
Therefore Reports and Tables are kept separate in databases.
For Calc i use a separate page for generated output an would combine A2 a CAHR(10) = newline and a Tab before Appending B2 (in your Case B3).