Description: Sometimes the user wants to re-order the rows in no specific sequence. Actually, many times it is useful to shuffle them, i.e., to re-arrange the rows in a random order. Currently, there is no simple way to do this. This is a pitty because a) it is a relatively simple task, and b) it is quite normal in data analysis, statistics, and simulations. I propose to add a 'Shuffle' option available in the 'Sort' menu. Steps to Reproduce: 1. you have a column in a specific order 2. you want to re-arrange the data in a random order 3. there is no option to do this Actual Results: you have to use a workaround Expected Results: there should be an option to shuffle the data within the Sort menu(s) I propose to add this option in 2 different places: 1) in Data -> Sort, add a third option so the sorting can be "Ascending", "Descending", and "Random" (other potential names: "no order", "shuffle", "un-order") 2) in Autofilter, add the "Shuffle" or "Random" option to the first 3 options, so the dialog would show "Sort Ascending", "Sort Descending", "Shuffle data" (other names: "Random order", "No order (random)", etc.) Reproducible: Always User Profile Reset: Yes Additional Info: The procedure would be quite simple. In pseudocode, it would look like this. To shuffle Column 1 (which has n rows): - create a Column 2 with n rows - populate Column 2 with the formula randbetween(0, n) - sort Column 1 using Column 2 (eg can be Descending or Ascending).
Created attachment 190810 [details] this is how it would look like in Autofilter
Created attachment 190811 [details] this is how it would look like in Data-Sort
Workaround with RAND() is on Ask.LO: https://ask.libreoffice.org/t/mix-rows-of-a-spreadsheet-by-random-or-somehow-else-fast/32819 I agree that this would be a welcome feature, form the point of view of using Calc in research. What do you think, UX/Design team?
(In reply to Stéphane Guillou (stragu) from comment #3) > I agree that this would be a welcome feature, form the point of view of > using Calc in research. > > What do you think, UX/Design team? I agree with this proposal... I often have to shuffle rows of data (not only values in a selection). I even wrote a few macros to help with that. Maybe we could have a new Uno command capable of: - Shuffling values in a cell range - Shuffling rows in a cell range - Shuffling columns in a cell range
(In reply to J22Gim from comment #0) > I propose to add a 'Shuffle' option available in the 'Sort' menu. Drawback is that the option bloats the UI. And it's rather unexpected to sort up/down and random. But the extra UNO command, as suggested by Rafael, to shuffle data sounds straightforward to me, at least for row/column. Don't get the "values in range".
There is mostly working but incomplete patch at https://gerrit.libreoffice.org/c/core/+/22963 If someone wants to complete it (see Eike's comments what's wrong).
(In reply to Heiko Tietze from comment #5) > (In reply to J22Gim from comment #0) > [...] And it's rather unexpected to > sort up/down and random. This could be useful for randomising sets, while keeping the sets ordered. For example, randomise the samples identified by their ID in column B, while keeping "Set 1", "Set 2", "Set 3" ordered in Column A. > Don't get the "values in range". I guess Rafael means something like sampling the values without redraw in a range of cells, which I think should be separate form the Sort dialog as it is not per se done in a row or column direction. We can already do something similar with Data > Statistics > Sampling. (In reply to Tomaz Vajngerl from comment #6) > There is mostly working but incomplete patch at > https://gerrit.libreoffice.org/c/core/+/22963 > If someone wants to complete it (see Eike's comments what's wrong). Thanks, Quikee! As I understand it, this covers the second part of what Rafael mentioned (cell range shuffling), but does not integrate it into the Sort dialog to offer the sort + randomise option, right?
(In reply to Stéphane Guillou (stragu) from comment #7) > Thanks, Quikee! As I understand it, this covers the second part of what > Rafael mentioned (cell range shuffling), but does not integrate it into the > Sort dialog to offer the sort + randomise option, right? I think it just adds a new command that shuffles the cell range, but that's the more difficult core change. There is no change to sorting or the sort dialog AFAIR. I was working on https://gerrit.libreoffice.org/c/core/+/22963 a couple of months ago, so it can works with a recent build and fixed some issues reported, but I forgot to push it and don't have the access to the machine now :(
I don't agree with that, »shuffle« is the complete opposite of »sorting«. If it is, then it should be another Option under →→Sheet→→Fill Cells→→Fill Random Numbers
(In reply to Werner Tietz from comment #9) > I don't agree with that, »shuffle« is the complete opposite of »sorting«. It's an almost identical action - shuffle is sort with an random order instead of derived from the content. > If it is, then it should be another Option under →→Sheet→→Fill Cells→→Fill > Random Numbers This has nothing to do with filling cells - it's shuffling existing cells, so why it should be added there. The only thing in common with the random number generator is that the actions do something randomly.
(In reply to Heiko Tietze from comment #5) > (In reply to J22Gim from comment #0) > > I propose to add a 'Shuffle' option available in the 'Sort' menu. > Drawback is that the option bloats the UI. And it's rather unexpected to > sort up/down and random. But the extra UNO command, as suggested by Rafael, > to shuffle data sounds straightforward to me, at least for row/column. Don't > get the "values in range". Nice!
(In reply to J22Gim from comment #11) > (In reply to Heiko Tietze from comment #5) > > (In reply to J22Gim from comment #0) > > > I propose to add a 'Shuffle' option available in the 'Sort' menu. > > Drawback is that the option bloats the UI. And it's rather unexpected to > > sort up/down and random. But the extra UNO command, as suggested by Rafael, > > to shuffle data sounds straightforward to me, at least for row/column. Don't > > get the "values in range". > > Nice! Agree. 'Sort' in this context is an umbrella term for 're-arrange the elements'. The re-arrangement could be either in a given order (ascending, descending) or in a purposedly 'unordered' fashion (i.e. random).
(In reply to Tomaz Vajngerl from comment #10) > (In reply to Werner Tietz from comment #9) > > I don't agree with that, »shuffle« is the complete opposite of »sorting«. > > It's an almost identical action - shuffle is sort with an random order > instead of derived from the content. > > > If it is, then it should be another Option under →→Sheet→→Fill Cells→→Fill > > Random Numbers > > This has nothing to do with filling cells - it's shuffling existing cells, > so why it should be added there. The only thing in common with the random > number generator is that the actions do something randomly. Agree. 'Sort' in this context is an umbrella term for 're-arrange the elements'. The re-arrangement could be either in a given order (ascending, descending) or in a purposedly 'unordered' fashion (i.e. random).
(In reply to Heiko Tietze from comment #5) > (In reply to J22Gim from comment #0) > > I propose to add a 'Shuffle' option available in the 'Sort' menu. > Drawback is that the option bloats the UI. And it's rather unexpected to > sort up/down and random. But the extra UNO command, as suggested by Rafael, > to shuffle data sounds straightforward to me, at least for row/column. Sometimes I also need the randomization and adding an extra column with random values just in order to do that is not always simple. I would welcome such a change. I find the extra radio button "Random" under Ascending and Descending is pretty straightforward. If it was really needed, a new menu item "Shuffle" could come in the Data menu under the Sort Descending but I don't think it is needed so often. Therefore, I would be happy if it was just an extra radio button in the Sort dialog, as the second attachment shows. I would like to have this feature. Regarding the various Shuffle options: >> - Shuffling values in a cell range >> - Shuffling rows in a cell range >> - Shuffling columns in a cell range > Don't get the "values in range". I interpret this as shuffling rows and columns moves the selected cells of a row/column together while shuffling values moves the individual cells independently from each other. For example, if cells [1], [2], [4] and [5] are selected in this table: 0 1 2 3 4 5 6 7 8 then the outcome of the individual shuffles could be this: - Shuffling values in a cell range 0 2 4 3 5 1 6 7 8 (only the selected values move, they move independently, unselected cells don't move) - Shuffling rows in a cell range 0 4 5 3 1 2 6 7 8 (selected cells of a row (4-5 and 1-2) move together, unselected cells don't move) >> - Shuffling columns in a cell range 0 2 1 3 5 4 6 7 8 (selected cells of a column (1-4 and 2-5) move together, unselected cells don't move) If my understanding is correct then I don't find these last two ones as useful as the first one, or as moving the complete rows/columns together, even the unselected cells.
I found the code and updated the https://gerrit.libreoffice.org/c/core/+/22963
We discussed the topic in the design meeting. The function is very much welcome. Adding it to the sort dialog is quite confusing given that you first sort asc by column A, then randomly by B, and last desc for C. Makes no sense. So we recommend to add the command "Unsort Selection" under Data next to the sort options. (an alternative to unsort would be shuffle; randomize might be misleading)
(In reply to Heiko Tietze from comment #16) > We discussed the topic in the design meeting. > > The function is very much welcome. Adding it to the sort dialog is quite > confusing given that you first sort asc by column A, then randomly by B, and > last desc for C. Makes no sense. In that case, I could imagine the UI greying out anything below a random sort. I still think having the random option in the Sort dialog could be expected for users working in research – but of course, happy to go with what the design team agreed on. > So we recommend to add the command "Unsort Selection" under Data next to the > sort options. (an alternative to unsort would be shuffle; randomize might be > misleading) In that case, such a function should offer "cells / rows / columns" to cover all use cases, including J22Gim's. Regarding the name, I don't think "unsort" is suitable as the data might not be sorted in the first place, users might understand it as "undo the last sort", and some users understand shuffling as a kind of sort. I'd vote for "shuffle".
(In reply to Stéphane Guillou (stragu) from comment #17) > In that case, I could imagine the UI greying out anything below a random > sort. Could be an option although involves some dev effort and might be surprising for users to loose all configuration after switching to random on key1. > In that case, such a function should offer "cells / rows / columns" to cover > all use cases, including J22Gim's. Why not using the selection? I'm hesitant adding too many choice to the menu.
(In reply to Heiko Tietze from comment #18) > (In reply to Stéphane Guillou (stragu) from comment #17) > > In that case, I could imagine the UI greying out anything below a random > > sort. > Could be an option although involves some dev effort and might be surprising > for users to loose all configuration after switching to random on key1. We already grey out Key 3 if Key 2 is not defined, so the mechanics are already there. (In reply to Heiko Tietze from comment #18) > (In reply to Stéphane Guillou (stragu) from comment #17) > > In that case, such a function should offer "cells / rows / columns" to cover > > all use cases, including J22Gim's. > Why not using the selection? I'm hesitant adding too many choice to the menu. I understand the command would shuffle what is selected, but we can do different things with one same selection: see the three options in comment 14. In my view, there's only two realistic scenarios here, to cover all use cases: A) add "random" to the existing Sort dialog for shuffling rows or columns, and add a separate Shuffle command that has one job: shuffling values in a cell range B) don't change the Sort dialog, but the new Shuffle command should open a dialog to choose between rows/columns/cells. To me, the benefits of integrating it into the Sort dialog are: - sort and randomise in one go ("randomise inside each level of a categorical variable", if you will), instead of using two dialogs (first, shuffle all rows; then, sort by categorical variable) - make use of existing useful functions in the Sort dialog: "Range contains row/column labels"; "Copy sort results to"...
(In reply to Stéphane Guillou (stragu) from comment #19) > A) add "random" to the existing Sort dialog... > B) new Shuffle command opens a dialog to choose between rows/columns/cells. A > B; let's what Tomaz can achieve easily.
(In reply to Stéphane Guillou (stragu) from comment #19) > (In reply to Heiko Tietze from comment #18) > > (In reply to Stéphane Guillou (stragu) from comment #17) > > > In that case, I could imagine the UI greying out anything below a random > > > sort. > > Could be an option although involves some dev effort and might be surprising > > for users to loose all configuration after switching to random on key1. > We already grey out Key 3 if Key 2 is not defined, so the mechanics are > already there. > > (In reply to Heiko Tietze from comment #18) > > (In reply to Stéphane Guillou (stragu) from comment #17) > > > In that case, such a function should offer "cells / rows / columns" to cover > > > all use cases, including J22Gim's. > > Why not using the selection? I'm hesitant adding too many choice to the menu. > I understand the command would shuffle what is selected, but we can do > different things with one same selection: see the three options in comment > 14. > > In my view, there's only two realistic scenarios here, to cover all use > cases: > A) add "random" to the existing Sort dialog for shuffling rows or columns, > and add a separate Shuffle command that has one job: shuffling values in a > cell range > B) don't change the Sort dialog, but the new Shuffle command should open a > dialog to choose between rows/columns/cells. > > To me, the benefits of integrating it into the Sort dialog are: > - sort and randomise in one go ("randomise inside each level of a > categorical variable", if you will), instead of using two dialogs (first, > shuffle all rows; then, sort by categorical variable) > - make use of existing useful functions in the Sort dialog: "Range contains > row/column labels"; "Copy sort results to"... > >"randomise inside each level of a categorical variable" That is a cool possibility! I didn't realize that one!