Help with permutation list.

[superbeast]

I am trying to create a list of permutations in a column for example I need a list of numbers 1-20 with no numbers repeating. This is causing my head to hurt and I have been working on this problem for some time. So any help would be much appreciated and is much needed.

 

[Jon von der Heyden]

This should get you started. Hope it works

Paste into a standard module and apply as per example below:

Function RandBetweenNoRept(rng As Range, lngStart As Long, lngEnd As Long)
    Dim lngLOOP As Long
 
    Do
        RandBetweenNoRept = Evaluate("=INT(RAND()*" & lngEnd & ")+" & lngStart)
        lngLOOP = lngLOOP + 1
    Loop While Application.CountIf(rng, RandBetweenNoRept) > 0 And lngLOOP< lngEnd
 
    If Application.CountIf(rng, RandBetweenNoRept) > 0 Then
        RandBetweenNoRept = CVErr(xlErrNA)
    End If
End Function

Excel Workbook
	H
6	Nums
7	15
8	1
9	5
10	3
11	13
12	12
13	2
14	6
15	9
16	16
17	14
18	10
19	20
20	4
21	7
22	18
23	17
24	8
25	11
26	19
Index

Excel 2003

Cell Formulas
Range	Formula
H7	=randbetweennorept($H$6:H6,1,20)

Note how I have fixed the start of the range ($H$6). This is so that as the formula is extended it will expand. The function uses this range to ensure that the number has been repeated within the range.
Will yield #N/A once all results have been returned (i.e. after 20 cells have been formulated)

 

[Jon von der Heyden]

Ok, this needs some refinement but I don't have time at the moment. It should be fine for 1 to 20, but won't for e.g. 3 to 30. So hopefully solves your problem.

Never-the-less I will pick this up again shortly.

 

[northwolves]

Try this,please

Sub macro1()
[a1:a20] = [row(a1:a20)]
[b1:b20] = "=rand()"
[a:b].Sort [b1]
[b:b] = ""
End Sub

Regards
Northwolves

 

[superbeast]

http://www.mrexcel.com/forum/member.php?u=31398Jon von der Heydon it works but I am using Excel 2008 so when I try to put it in different cells it starts to repeat. However if I put it into the H7-H26 cells it still works and I am trying to generate multiple lists so for example 1-20 6 times. after the first set it starts returning repeats. I need it also to work for 1-4, 1-6, 1-8, 1-10, 1-12 as well. And I need it to be able to produce 1-4 30 times, 1-6 20 times, 1-8 15 times, 1-10 12 times, 1-12 10 times and 1-20 6 times.

 

[northwolves]

It works but I am using Excel 2008 so when I try to put it in different cells it starts to repeat. However if I put it into the H7-H26 cells it still works and I am trying to generate multiple lists so for example 1-20 6 times. after the first set it starts returning repeats. I need it also to work for 1-4, 1-6, 1-8, 1-10, 1-12 as well. And I need it to be able to produce 1-4 30 times, 1-6 20 times, 1-8 15 times, 1-10 12 times, 1-12 10 times and 1-20 6 times.

Nothing is impossible,Provide the layout of your data,please.

Regards
Northwolves

 

[superbeast]

Nothing is impossible,Provide the layout of your data,please.

Regards
Northwolves

Okay I am not sure what you mean but like the example I gave I am trying to come up with a set of 120 numbers between these sets 1-4, 1-6, 1-8, 1-10 ,1-12, and 1-20. So for example I need a total of 30 permutations with no numbers repeating of the number set 1-4 in one column.

 

[superbeast]

Also I am grateful for the help you guys are providing so thanks a lot.

 

[northwolves]

Not very sure what you need,try the following codes:

Sub macro1()
For i = 0 To 4
makearray [h6].Offset(, i), 24 '120=5*24
Next
End Sub

Sub makearray(ByVal r As Range, ByVal n As Long)
With r.Resize(n)
.Formula = "=row(a1)"
.Value = .Value
.Offset(, 1) = "=rand()"
.Resize(, 2).Sort r.Offset(, 1)
.Offset(, 1) = ""
End With
End Sub

Regards
Northwolves

 

[northwolves]

And you can get 30 columns 1-4 by change the parameter n only.

Sub macro1()
For i = 0 To 29
makearray [a1].Offset(, i), 4 '120=30*4
Next
End Sub

Regards
Northwolves