Method for randomising order of appearance of two-digit number sets without repetitions of any number sets?

[appreciatethehelp]

Mojave 10.14.6, LO 6.2.5.2

I’m trying to develop a method to randomise the order of appearance of two-number sets (over two cells in the same row e.g. A1=1, B1=2; A2=3, B2=5 etc.) without repeating any sets or repeating the same number twice in each set.

I’ve attached the list of number sets (210 in total) here.

So to be clear the result should be a 2-column array consisting of of same 210 horizontal sets of numbers provided, but in random order, as opposed to the numbers increasing in a linear fashion as do the sets already provided.

Cheers.Number sets.ods

[JohnSUN]

If you want to do this only with formulas, then perhaps a discussion of this issue will help you.

I think this is one of those cases where a macro makes the task much easier. A not very complicated function can quickly shuffle the original array:

Function randReorder(aData As Variant) As Variant
Dim countPairs As Long 
Dim i As Long, j As Long
Dim tmpSwap As Variant
Dim nextRnd As Long
	Randomize
	countPairs = UBound(aData) - LBound(aData) + 1
	For i = LBound(aData) To UBound(aData)
		nextRnd = Int((countPairs * Rnd) + 1)
		For j = LBound(aData,2) To UBound(aData,2)
			tmpSwap = aData(nextRnd, j)
			aData(nextRnd, j) = aData(i, j)
			aData(i, j) = tmpSwap
		Next j
	Next i
	randReorder = aData()
End Function

Here’s how this function can be applied to your data: enter in C2 =RANDREORDER($A$2:$B$211) and press Ctrl+Shift+Enter. The attached file contains your data, macro and three sets of mixed pairs -
RandomReorder.ods