Excel Function to fill grid with specific data that is limited in how many times a data point can be used

Question

I am trying to fill in an excel grid 16 columns across by 12 rows down with 19 different data points. Each point can only be used a set number of times (different for each point) and the data point can not duplicate itself within one cell in any direction. Example:

Data Points A,B,C,D,E

A,B,C,D,E

D,E,A,B,C

B,C,D,E,A

Is there a formula (or set of formulas) that I can use to create a random "pattern" of data points, that takes into account the limited amount each point can be used AND checks to see if that result is in a neighboring cell?

Data Points Available
A   21
B   17
C   14
D   8
E   7
F   6
G   4
H   3
I   3
J   3
K   2
L   1
M   1
N   1
O   1
P   1
Q   1
R   1
S   1

tigeravatar · Answer 1 · 2019-05-06T16:46:55.173

If you have Office 365 or Excel 2016+, then you can use the TEXTJOIN function to do this. If you have an older version of Excel, then this UDF (originally created by Scott Craner):

'TEXTJOIN UDF created by Scott Craner at www.stackoverflow.com on Aug 23 2017
'Code from https://stackoverflow.com/questions/45843881/textjoin-udf-for-excel-2013
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Then create a data setup that looks like this:

Column A contains the value (data point) that will be represented in the grid. Column B contains the Total Qty that value can be chosen.

The table in V2:AK13 contains strings showing available Values from column A, delimited by | (this lets you have data points in column A that are variable lengths, they don't all have to be a single character). The formula used in cell V2 and copied over and down is:

=IFERROR(textjoin("|",TRUE,INDEX(REPT($A$2:$A$20,($B$2:$B$20-COUNTIF($D$1:$S1,$A$2:$A$20)-COUNTIF($C2:C2,$A$2:$A$20)>0)*((D1<>$A$2:$A$20)*(C2<>$A$2:$A$20))),)),"")

Lastly, for the final output grid, in cell D2 and copied over and down is this formula:

=IF(V2="","",TRIM(MID(SUBSTITUTE(V2,"|",REPT(" ",LEN(V2))),LEN(V2)*RANDBETWEEN(0,LEN(V2)-LEN(SUBSTITUTE(V2,"|","")))+1,LEN(V2))))

You can hide columns U:AK if preferred, or cut/paste that temp table to a different sheet.

Note that this approach makes a few assumptions:

No duplicates in adjacent cells where adjacent is defined as above, below, left or right (diagonal duplicates are ok)
Results grid should be populated left to right first, then top to bottom

If Adjacent should be defined such that diagonal duplicates are also prohibited, then the formula in cell V2 and copied over and down becomes this:

=IFERROR(textjoin("|",TRUE,INDEX(REPT($A$2:$A$20,($B$2:$B$20-COUNTIF($D$1:$S1,$A$2:$A$20)-COUNTIF($C2:C2,$A$2:$A$20)>0)*((D1<>$A$2:$A$20)*(C2<>$A$2:$A$20)*(C1<>$A$2:$A$20)*(E1<>$A$2:$A$20))),)),"")

If the Results grid should be populated top to bottom first, then left to right, the formula in cell V2 and copied over and down becomes this:

=IFERROR(textjoin("|",TRUE,INDEX(REPT($A$2:$A$20,($B$2:$B$20-COUNTIF($C$2:C$13,$A$2:$A$20)-COUNTIF(D$1:D1,$A$2:$A$20)>0)*((D1<>$A$2:$A$20)*(C2<>$A$2:$A$20)*(C1<>$A$2:$A$20)*(C3<>$A$2:$A$20))),)),"")

Excel Function to fill grid with specific data that is limited in how many times a data point can be used

1 Answers1