[Solved] How to randomly sort a set of items?

[Louseille]

Hello guys

Basically, I have a project that requires me to randomize a large set of items, say 200,000 or more.

My idea is this: select a column. Then, input one item in each cell. Then use a function that allows me to randomly reorder the column cells as many times as I want.

Is there such a function?


Thanks!

Rouseille<3~

[JohnSUN-Pensioner]

Add another column, fill it with

Code: Select all

=RAND()

Sort by this column.

[JohnV]

reorder the column cells as many times as I want.

Then to resort do Ctrl+Shift+F9 to reset the random numbers then sort again.

[karolus]

Hallo

reorder the column cells as many times as I want.

Then to resort do Ctrl+Shift+F9 to reset the random numbers then sort again.

RAND() recalculate meanwhile sorting..

Karolus

[JohnSUN-Pensioner]

Yes, in most cases, would require pressing Ctrl + Shift + F9. But not for this function.
Some special functions - RAND(), NOW(), and a few more - are recalculated when any change of the sheet on which they are located.
Sort operation - is a change contents of the worksheet. That means the entire column with RAND() will be recalculated immediately. Provided that the automatic calculation of cells activated (Tools - Cell Contents - AutoCalculate).

Oh, karolus fired faster

[Charlie Young]

Shameless plug: our Array Sort add-in function sorts formula results and not the formulas themselves, and may be applicable here. In fact, most of my testing of it was with random data generated with formulas. I also have a few enhancements to it I haven't posted yet.

[Louseille]

Hmm, it does work, though both sorting orders don't seem to arrange the numbers from smallest to highest or vice versa. If the sorting worked I can probably use RANDBETWEEN() instead of RAND() right?

Thanks people!

[acknak]

Hmm, it does work, though both sorting orders don't seem to arrange the numbers from smallest to highest or vice versa.

Appearances can be deceiving. The values are sorted correctly but as soon as the sort is finished, new random values replace the sorted values because of the automatic recalculation and the RAND function.

If you turn off Calc's recalculation, you should see that the sorting is done correctly.

If you use RANDBETWEEN instead, which does not automatically recalculate, the random values will remain the same until you explicitly force a recalculation (F9).

[zdanman]

I created a Macro module to do this automatically with a click of a button... simply select a range of cells then run the macro (attach a button of this macro to the toolbar to make it even easier).

It will automatically output a column (full of "=rand()" ) to the right of the selected column and sort the selection by the rand() column.

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub

sub AceSort
	rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
	rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	sel = ThisComponent.getCurrentSelection()
	range = sel.getRangeAddress()
	sheet = ThisComponent.Sheets(range.Sheet)

	rem ----------------------------------------------------------------------
	rem physical rows of sheet
	dim phyRow as integer
	dim phyCol as integer

	rem output rand() to next column cells
	phyCol = range.StartColumn
	for iRow = 0 to (range.EndRow-range.StartRow)
		phyRow = range.StartRow + iRow
		sheet.getCellByPosition( phyCol + 1, phyRow ).setFormula( "=rand()" )
	next iRow

	dim oSortFields(0) as new com.sun.star.util.SortField
	dim oSortDescs(0) as new com.sun.star.beans.PropertyValue

	sortrange = sheet.getCellRangeByPosition(range.StartColumn, range.StartRow, range.StartColumn + 1, range.EndRow)
	oSortFields(0).Field = 1
	oSortFields(0).SortAscending = TRUE
 	oSortDescs(0).Name = "SortFields"
 	oSortDescs(0).Value = oSortFields()
	sortrange.Sort(oSortDescs())

end sub

[JohnSUN-Pensioner]

Good job, zdanman!
But IMHO if you record macro then you can reshuffle rows of selection in memory (without column "=rand()"), why not?

Code: Select all

Sub ShflSelection
Dim oSels As Variant
Dim aData As Variant
Dim i&, lB&, uB&
Dim Index() As Long 
Rem TODO: Verify that it is a Calc workbook!
  oSels = ThisComponent.getCurrentSelection()
REM TODO: Verify that oSels is a single range (not multiply selection)
  aData = oSels.getDataArray()
  lB = LBound(aData())
  uB = UBound(aData())
  If lB >= uB Then Exit Sub	' No rows to shuffle
  Index = ShflIndex(uB-lB+1)
Dim aNewRows(lB To uB) As Variant
  For i = lB To uB	' Shuffle
    aNewRows(i) = aData(Index(i))
  Next i
  oSels.setDataArray(aNewRows)
End Sub

Function ShflIndex(cntCard  As Long) As Variant
Dim Deck(cntCard-1) As Long
Dim i As Long
Dim tmpCard As Long
Dim nextRnd As Long
For i = LBound(Deck) to UBound(Deck)
  Deck(i) = i
Next i
Randomize
For i = LBound(Deck) to UBound(Deck) ' Each element is swapped with a random element
  nextRnd = Int((cntCard * Rnd))
  tmpCard = Deck(nextRnd)
  Deck(nextRnd) = Deck(i)
  Deck(i) = tmpCard
Next i
ShflIndex = Deck() ' Have an array of shuffled indexes
End Function

[karolus]

Hallo

And the job is done by 6 loc in python:

Code: Select all

from random import shuffle

def shuffle_calc_selection(*_): # optional dummyArg for the sake calling via Button
    sel = XSCRIPTCONTEXT.getDocument().getCurrentSelection()
    data = list(sel.DataArray)
    shuffle(data)
    sel.DataArray = tuple(data)