Step through a single permutation order based on a number provided

Question

I have 5 items that can be placed in any unique order, I want to store the values (numbers) of a single unique order to a variable, one by one. For example:

User input: 7

Then i_Int = 7

should give me

v_Var = 1

wait 1 sec

v_Var = 3

wait 1 sec

v_Var = 2

wait 1 sec

v_Var = 4

wait 1 sec

v_Var = 5

The data below list all possible permutations of 5 items, where the first row lists the permutation #, I will not have this data to make things easy.

1   1   2   3   4   5
2   1   2   3   5   4
3   1   2   4   3   5
4   1   2   4   5   3
5   1   2   5   3   4
6   1   2   5   4   3
7   1   3   2   4   5
8   1   3   2   5   4
9   1   3   4   2   5
10  1   3   4   5   2
...
111 5   3   2   1   4
112 5   3   2   4   1
113 5   3   4   1   2
114 5   3   4   2   1
115 5   4   1   2   3
116 5   4   1   3   2
117 5   4   2   1   3
118 5   4   2   3   1
119 5   4   3   1   2
120 5   4   3   2   1

It isn't clear what you are trying to do. What is this "wait 1 second" all about? Are you trying to step through a *single* permutation (which is what the title of your question is) or *all* permutations? What part are you having difficulty with? What have you tried? — John Coleman, Sep 06 '18 at 23:00
Here's what I think is being asked: *Given a set of 5 things, there are 120 possible sets of permutations where order matters and things cannot be repeated. The user may enter a number from 1 to 120 to select one of the sets. How do I dynamically generate the chosen set?* It's an interesting question. Sadly, I don't know the answer. Maybe this should first be asked on a math site. Once the math is known, coding should be trivial. — StoneGiant, Sep 06 '18 at 23:57
If @StoneGiant is right, OP is looking to *unrank* permutations in lex order. [This question](https://stackoverflow.com/q/8940470/4996248) discusses the problem. — John Coleman, Sep 07 '18 at 00:12
Since you have 5 items, using 5 as your example of input is confusing. It isn't obvious that the 5 in the input isn't the same as the number of objects. Why not use e.g. 7 in the question, so that it is clear that the input is a row number and not the number of objects? — John Coleman, Sep 07 '18 at 12:36
And why wait 1 sec? Confusing too. Although this is a challenging question, VBA is not the best language solving it. What is the max of items you want to be aböe to compute? I think VBA has `Decimal(28,0) as max number (about 2^95- or 10^28) what limits you to n=27 as count of permutations is `n!`. Other languages can use `BigInteger` almost unlimited. — ComputerVersteher, Sep 07 '18 at 14:11
@ComputerVersteher We have a program that 'feeds off' Excel VBA, when the user enters, let's say 7; the program needs all the values feed to it one by one. — Waqas Tariq, Sep 07 '18 at 17:40

score 0 · Accepted Answer · answered Sep 09 '18 at 12:00

Here is a function that returns the permutation of 1,...,n of rank i:

Function Unrank(ByVal n As Long, ByVal rank As Long, Optional lb As Long = 1) As Variant
    Dim Permutation As Variant
    Dim Items As Variant
    ReDim Permutation(lb To lb + n - 1)
    ReDim Items(0 To n - 1)
    Dim i As Long, j As Long, k As Long, q As Long
    Dim fact As Long

    For i = 0 To n - 1
        Items(i) = i + 1
    Next i
    rank = rank - 1
    j = lb
    For i = n - 1 To 1 Step -1
        fact = Application.WorksheetFunction.fact(i)
        q = Int(rank / fact)
        Permutation(j) = Items(q)
        'slide items above q 1 unit to left
        For k = q + 1 To i
            Items(k - 1) = Items(k)
        Next k
        j = j + 1
        rank = rank Mod fact
    Next i
    'place last item:
    Permutation(lb + n - 1) = Items(0)
    Unrank = Permutation
End Function

As a default, it returns the result as a 1-based array. To make it 0-based, use a call like Unrank(5,7,0). As a test:

Sub test()
    'fills A1:A120 with the permutations of 1,2,3,4,5
    Dim i As Long
    For i = 1 To 120
        Cells(i, 1).Value = Join(Unrank(5, i), " ")
    Next i
End Sub

13! is too large to hold in a Long variable, so the code throws an untrapped error when n=14. The algorithm that I use depends on the ability to do modular arithmetic with the relevant factorials, so there is no easy fix in VBA. Note that you could easily tweak the code so that you pass it an array of items to permute rather than always permuting 1-n. The algorithm destroys the array Items, so such a tweak would involve creating a 0-based (so that the modular arithmetic works out) copy of the passed array.

Step through a single permutation order based on a number provided

1 Answers1