Results 1 to 3 of 3

Thread: Permutations of sets of values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Permutations of sets of values

    Hello,

    I'm a new VBA user and really need some help. I'm trying to perform pairwise permutations of an undetermined number of values entered along a single row. The permutations should be exported into columns A and B in another tab. Then I want to repeat this process for the next and all subsequent rows containing values, and I want these permutations to be subsequently entered in the same columns as the previous permutations i.e., A and B in the other tab.

    Tab 1 looks like (Cell A1 is row 1, column A)

    A1 A2 A3 A4
    B1 B2 B3 B4
    C1 C2 C3 C4

    Tab 2 Should look like (Entries in column A, B)

    A1 A2 Permutation Set 1 (From Row 1)
    A1 A3
    A1 A4
    A2 A1
    A2 A3
    A2 A4
    A3 A1
    A3 A2
    A3 A4
    A4 A1
    A4 A2
    A4 A3
    B1 B2 Permutation Set 2 (From Row 2)
    B1 B3
    B1 B4
    B2 B1
    B2 B3
    B2 B4
    B3 B1
    B3 B2
    B3 B4
    B4 B1
    B4 B2
    B4 B3
    C1 C2 Permutation Set 3 (From Row 3)
    C1 C3
    C1 C4
    C2 C1
    C2 C3
    C2 C4
    C3 C1
    C3 C2
    C3 C4
    C4 C1
    C4 C2
    C4 C3

    I have been working with the code below, most of which was kindly provided by an expert from this forum. The code will perform pairwise permutations on the first row values starting in A1, Tab 1, and will output the permutation values to columns A and B in Tab 2, but I haven't been able to 1) cycle through subsequent rows in Tab 1, and 2) add the permutations to the previous permutations in the same two columns (A, B) in Tab 2.

    Any help would be really appreciated!



    Sub Permutations()




    Dim rRng As Range, p
    Dim vElements, lRow As Long, vresult As Variant




    Set rRng = Worksheets("Tab 1").Range("A1", Range("A1").End(xlToRight))
    p = 2 ' Pairwise permutations

    vElements = Application.Index((rRng), 1, 0)
    ReDim vresult(1 To p)
    Application.ScreenUpdating = False
    Call PermutationsNP(vElements, CInt(p), vresult, lRow, 1)
    Application.ScreenUpdating = True
    End Sub

    Sub PermutationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iIndex As Integer)
    Dim i As Long, j As Long, bSkip As Boolean

    For i = 1 To UBound(vElements)
    bSkip = False
    For j = 1 To iIndex - 1
    If vresult(j) = vElements(i) Then
    bSkip = True
    Exit For
    End If
    Next j
    If Not bSkip Then
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
    lRow = lRow + 1
    Worksheets("Tab 2").Range("A" & lRow).Resize(, p) = vresult 'Send permutations to Tab 2, column A, B
    Else
    Call PermutationsNP(vElements, p, vresult, lRow, iIndex + 1)
    End If
    End If
    Next i




    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Permutations of sets of values

    Try this:-
    Code:
    Sub MG20Aug13
    Sub Permutations()
    Dim rRng As Range, p
     Dim vElements, lRow As Long, vresult As Variant, n
    Dim Dn As Range, c As Long, Ray()
      
    Set rRng = Worksheets("Tab 1").Range("A1").CurrentRegion
    For Each Dn In rRng
        c = c + 1
        ReDim Preserve Ray(c)
        Ray(c) = Dn
    Next Dn
    p = 2 ' Pairwise permutations
    
     vElements = Ray
     ReDim vresult(1 To p)
     Application.ScreenUpdating = False
     Call PermutationsNP(vElements, CInt(p), vresult, lRow, 1)
     Application.ScreenUpdating = True
     
     End Sub
    
     Sub PermutationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iIndex As Integer)
     Dim i As Long, j As Long, bSkip As Boolean
    
     For i = 1 To UBound(vElements)
    bSkip = False
    For j = 1 To iIndex - 1
    If vresult(j) = vElements(i) Then
    bSkip = True
    Exit For
    End If
    Next j
    If Not bSkip Then
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
    lRow = lRow + 1
    Worksheets("Tab 2").Range("A" & lRow).Resize(, p) = vresult 'Send permutations to Tab 2, column A, B
    Else
    Call PermutationsNP(vElements, p, vresult, lRow, iIndex + 1)
    End If
    End If
     Next i
    
    
    
    End Sub
    Regards Mick

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Permutations of sets of values

    Many thanks Mick That's almost got it! I believe your code is calculating all permutations of all values in all rows.

    Sample output of the first 16 rows in Tab 2:

    A1 A2
    A1 A3
    A1 A4
    A1 B1
    A1 B2
    A1 B3
    A1 B4
    A1 C1
    A1 C2
    A1 C3
    A1 C4
    A2 A1
    A2 A3
    A2 A4
    A2 B1
    A2 B2

    I'd like to calculate a discrete set of permutations corresponding to each row (in Tab 1) individually. So in my example there would be a set of permutations consisting of pairs of only A values, and another set of permutations consisting of pairs of only B values, but no permutations containing pairs of A and B values.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •