Oct 03 2019 12:33 AM
Hi,
I am making a pivot table aka database in excel. I have a column to fill in randomly with the values "Yes" or "No". I do know how to fill in random values, however I need a fixed proportion/ratio being 40% "Yes" and 60% "No". So in my database with 1000 records I need 400 saying "Yes" and 600 saying "No". But I do not want to give the first 400 a "Yes" and the remaining 600 a "No", I want them randomly.
Is there any way to do this?
Thanks in advance!
Oct 03 2019 01:52 AM
You may try something like this and tweak it if required.
Sub RandomYesNo()
Dim y() As Variant
Dim No As Object
Dim it As Object
Dim i As Long
Dim lr As Long
Dim NoCount As Long
'Defining the No Ratio %, change it if required
Const ratioNo As Double = 0.6
'Finding last row with data in the data set
'The below line finds the last row with data in column A
lr = Cells(Rows.Count, 1).End(xlUp).Row
NoCount = Round((lr - 1) * ratioNo, 0)
ReDim y(1 To lr - 1, 1 To 1)
Set No = CreateObject("Scripting.Dictionary")
Do While No.Count < NoCount
i = WorksheetFunction.RandBetween(2, lr)
If Not No.exists(i) Then
No(i) = ""
y(i - 1, 1) = "No"
End If
Loop
For i = 1 To UBound(y, 1)
If y(i, 1) = "" Then y(i, 1) = "Yes"
Next i
'Writing the Yes/No in the column B, change it if required
Range("B2").Resize(UBound(y, 1)).Value = y
End Sub
In the attached, click the button called "Generate Random Yes/No" on Sheet1 to run the code and it will generate Random Yes/No in column B.
Oct 03 2019 04:51 AM
Hi @brooij_bdr
If you want a solution of your requirement using excel functions, you can use attached file that will always generate desired proportion of 'Yes/No' in different positions.
Thanks
Tauqeer