Random N Numbers in an Array

[ionelz]

Hi,
I have this problem :
01. a variable rows N from 1 to 50, in sample N=9 (A1=9)
02. a variable columns C from 1 to 5, in sample C=5 (A2=5)
03. a Requirement R=2 (A3=2)

I did a manually but is difficult when numbers increase
Calculation :
Total of "X"=N*R=18
Limits L=N*R/C=9*2/5=3.6
Since L is not integer, Limits are 3 and 4 ,Integer(L) and integer(L)+1

I need this : every row to have R=2 of "X" and every column 3 and 4

 

[Legacy 143009]

Is the distribution of L also going to be random?
In addition, are you ok with VBA?

 

[ionelz]

Is the distribution of L also going to be random?
In addition, are you ok with VBA?

Thank you for answer and for willing to look into
Yes, L (is the same if INTEGER) or if is not integer then L is Random
Again
- Total of "X" is N*R=18
- Total of L is same N*R=18
- each N have R=2 of "X" in row of N
- each column have L of "X" or (L1 , L2)

If Formula is not possible, then VBA

 

[Legacy 143009]

Hi!

Please find the sample file. You can expand downwards and rightwards.

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

Because the code works heuristic, sometimes it stucks in such scenarios. But 99% of the time it works

 

[Fluff]

@Flashbond please post the code to the thread as per rule#4. Thanks

 

[Legacy 143009]

Sorry @Fluff you are right. This code works for the setup below:

Sub test()
  Dim lRow As Long, lCol As Long, maxCol As Long, L As Long, valR As Long, valL As Double
  valR = Cells(1, 3).Value
  valL = Cells(2, 3).Value
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  lCol = Cells(3, Columns.Count).End(xlToLeft).Column
  With WorksheetFunction
  Do While .CountA(Range(Cells(4, 2), Cells(lRow, lCol))) < (lRow - 3) * valR
    For c = 2 To lCol
    L = IIf(maxCol = .RoundUp((lCol - 1) / 2, 0), .RoundDown(valL, 0), .RoundUp(valL, 0))
      For r = 4 To lRow
        If .CountA(Range(Cells(r, 2), Cells(r, lCol))) < valR And .CountA(Range(Cells(4, c), Cells(lRow, c))) < L Then
          If .RandBetween(0, 1) Then
            Cells(r, c).Value = "X"
          End If
        End If
      Next
      maxCol = 0
      For i = 2 To lCol
        If .CountA(Range(Cells(4, i), Cells(lRow, i))) = .RoundUp(valL, 0) Then
          maxCol = maxCol + 1
        End If
      Next
    Next
  Loop
  End With
End Sub

 

[ionelz]

Thank you so much
So far, if I expand N , Excel freeze

 

[Legacy 143009]

Horizontally or vertically? How much do you expand? I am asking because I will replicate the same scenario and debug it like that.

 

[ionelz]

I just went Down, a little for N and Excel freeze
Over 12 , do not work, I would like to go to max 50
Also, it freeze Horizontal too, Max is 5, but I removed 5 (deleted) so I have 1,2,3,4 and it freeze too

 

[ionelz]

Also I see that Limits do not work, I mean VBA consider only LOWER limit
If L =4.4 , I have vertical only 4 "X" , I should have 4 and 5 otherwise I can not get TOTAL of "X"