Random selection with a caveat / criteria

Question

I have a 6 x 6 grid. I have 36 samples split into three equal groups (A, B, and C).

I want a formula to arrange the samples in that grid randomly. However each row and each column must contain two of each group. Doing this manually once or twice is easy, but there are a massive number of combinations.

I am open in terms of software choices so long as it is freeware or Microsoft Office based.

Thank you!

Answer 1

I put together a tool for generating random row/column permutations with a couple of formulas and some VBA. The sheet layout looks like this:

The reference grid is a trivial example of a valid matrix, as posted in Gary's Student's preliminary answer (possibly since deleted). The row and column permutations incorporate all possible unique combinations of permutations for the 6x6 grid. (This could easily be modified to include non-unique permutations, if desired.) The values in E12:E26 and L12:L26 are randomly seeded to either zero or one, to provide the basis for whether or not to perform a given permutation. Columns D and K just convert these to boolean values for simplified handling within the VBA (see below). The permuted grid is generated by the custom function doSwap, entered as an array formula. Pressing F9 to trigger sheet recalculation causes the various RAND functions to re-generate their random values, changing the series of permutations to be performed.

The VBA code that enables this behavior is:

Function doSwap(srcRg As Range, rowSwaps As Range, colSwaps As Range) As Variant
    Dim workVt As Variant
    Dim iter As Long

    workVt = srcRg.Value

    ' Do row swaps
    For iter = 1 To rowSwaps.Rows.Count
        With rowSwaps
            If .Cells(iter, 3).Value Then
                workVt = swapRow(workVt, .Cells(iter, 1), .Cells(iter, 2))
            End If
        End With
    Next iter

    ' Do col swaps
    For iter = 1 To colSwaps.Rows.Count
        With colSwaps
            If .Cells(iter, 3).Value Then
                workVt = swapCol(workVt, .Cells(iter, 1), .Cells(iter, 2))
            End If
        End With
    Next iter

    ' Store and return
    doSwap = workVt

End Function

Function swapCol(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
    Dim tempVal As Variant, workVt As Variant
    Dim iter As Long

    ' Check if Range or Array input
    If IsObject(inArr) Then
        If TypeOf inArr Is Range Then
            workVt = inArr.Value
        Else
            swapCol = "ERROR"
            Exit Function
        End If
    Else
        workVt = inArr
    End If

    ' Just crash if not correct size
    ' Do swap
    For iter = LBound(workVt, 1) To UBound(workVt, 1)
        tempVal = workVt(iter, idx1)
        workVt(iter, idx1) = workVt(iter, idx2)
        workVt(iter, idx2) = tempVal
    Next iter

    ' Return
    swapCol = workVt

End Function

Function swapRow(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
   Dim tempVal As Variant, workVt As Variant
   Dim iter As Long

    ' Check if Range or Array input
    If IsObject(inArr) Then
        If TypeOf inArr Is Range Then
            workVt = inArr.Value
        Else
            swapRow = "ERROR"
            Exit Function
        End If
    Else
        workVt = inArr
    End If

    ' Just crash if not correct size
    ' Do swap
    For iter = LBound(workVt, 2) To UBound(workVt, 2)
        tempVal = workVt(idx1, iter)
        workVt(idx1, iter) = workVt(idx2, iter)
        workVt(idx2, iter) = tempVal
    Next iter

    ' Return
    swapRow = workVt

End Function

The above code is not well robustified, but serves the present purpose. Extension/generalization should be pretty straightforward, if needed. In particular, it should handle as-is any size of 2-D reference grid, even one that is non-square. The key thing is to ensure the arrays of permutation instructions are set up properly.

EDIT: After playing with it a bit, it's clear this solution doesn't provide access to the full space of possible permutations. So, I tweaked it by adding a random "bit-shift" to swap the type labels among themselves. To simplify things, I switched from ABC labels to 123 labels, which allows implementation by a simple MOD operation, and also a quick-glance sanity check in the form of row and column sums:

Answer 2

There is a very simple way to accomplish this. First pre-assign slots to each of the three types:

Then take the first sample, for example SAMPLE_A_1, and place it randomly in one of the A-slots. Then continue to process each of the remaining 35 samples.


If this approach is acceptable, I'll post a short program to populate the matrix. If the approach is not acceptable, I will delete this post.