Pseudorandomisation

[Biopsy]

Dear Excel Experts,

I have a list of all possible order comibnations (it is 720) of 6 numbers, so 1-2-3-4-5-6, 1-2-3-4-6-5,... (see attachement, A1:F720).
From this list I would like to pseudorandomly draw X combinations in a way that each number is equally often on each position. In column J:O I gave an example of 6 combinations but it would be great if it is also possible with 120 combinations for example.
Could you help me with this please?

Thank you!

[AliGW]

No attachment ...

[Biopsy]

Sorry. Here it comes.

[shg]

	i	j	k	l	m	n	o	p	q
1	83	1	5	3	6	2	4		i1: =randbetween(1, 720)
2	491	5	1	3	6	4	2		j1:o1: {=index(a1:f720, i1, 0)}
3	569	5	4	6	1	2	3
4	260	3	1	6	5	2	4
5	61	1	4	5	6	2	3
6	630	6	2	3	5	1	4

[Biopsy]

Thank you!
But how does this formula control for that every number is in every column euqally often?
So when I draw 120 combinations e.g., then column A contains 20 x 1, 20 x 2, 20 x 3, 20 x 4, 20 x 5, 20 x 6 and so does column B - F.
Thank you!

[avk]

Try with in "H1"

Please Login or Register  to view this content.

copy paste down till end.
"J1"

Please Login or Register  to view this content.

copy paste across.

[davsth]

I think what you are asking is incredibly difficult to to, if I understand what you are asking correctly 20 of each of the 6 numbers in each of the six columns. Trying to apply this with randomness is not easy to do, when you get towards the bottom of the list it is virtually impossbile

[Biopsy]

Yes, you are right, it is really difficult and I have been trying to come up with a solution for a while. Yes, at the bottom of the list it is almost impossible.
But what about a formula that draws 60 random combinations out of the 720 possible combinations in which 10 of each of the 6 numbers are in each of the six columns?
Would that be possible?
That would already help me a lot.