Random Data Sampling

[a4arnav]

Hi,
I'm a VBA beginner looking for the below support here
I have prepared a sample tool basis of my little knowledge, but it requires the first two columns to be kept blank for sampling calculations
I also need help on stratified random sampling section, if the option selected is Proportional to stratum size. Request for help.

Details
Stratum Variable : This list box will have the names of the variables in the data range. First row of the range contains the variable names/column headers, then these names appear in this list box.

#Strata : This text box will show the number unique values from the above selected Stratum Variable. And next to this there is an option to view those unique values in a list box.

Stratified Random Sampling

(1) Equal from each stratum - On specifying the number of records, it highlights sample with same number of records from each stratum.
For example: if “Quarter” is the selected Stratum Variable and 1 as #Each Records, it will highlight total 4 samples 1 each from each quarters (Q1 to Q4)

(2) Proportional to stratum size - On specifying the number of records (total samples), it shall highlight the samples proportionate to total count of each stratum.
For example: if “Quarter” is the selected Stratum Variable and 10 as #Total Records, it shall highlight total 10 samples based on each quarter count(Q1 to Q4).
Attached a workbook with Sampling formula for reference.

Request help in the following area-
1. Currently Simple Random Sampling and Stratified Random Sampling with Equal from each stratum method are working based on the RANDBETWEEN formula in column B. Request your help if this can be done without the support of a column (A&B).

2. Stratified Random Sampling with Proportional to stratum size method. (attached a workbook with Sampling formula for reference)

Thanks in advance.

This was posted in one another excel forum, but no response hence posting here.

 

[a4arnav]

Attaching the revised version. I have managed to sort out the first point in #1 (Moved the sampling calculation from columns A & B to towards the end of the column)
Now, help required on the stratified sampling methods. Both equal and proportionate

 

[a4arnav]

Can anybody help me in - how to use the below simple random sampling codes for stratified sampling-
Based on the values in a selected field - both equal and proportionate methods

Sub Sampling()
Dim LR      As Long                            'Last Row
Dim LC      As Long                            ' Last Column
Dim SS      As Long                            'Sample Size
Dim mycell As Range

SS = Me.txtRandomCount.Text   
    With ActiveSheet
        .Cells(1, LC + 1) = "Sample"
        .Range(Cells(2, LC + 1), Cells(LR, LC + 1)).Name = "Sample"
        [Sample] = "=rand()"
        [Sample] = [index(rank(Sample,Sample),)]
        For Each mycell In [Sample]
            If mycell.Value <= SS Then
                mycell.Value = "Yes"
            Else
                mycell.Value = Empty
            End If
        Next mycell
    End With
End Sub