VBA - Nested functions with dynamic changes

[KosieNakata]

Hi Excel experts!! I'm trying to write a macro where it needs to extract 30% of the data from a dataset, where the 30% of data is selected randomly by using an assigned random number using the function RAND() first. The below is a quick overview of the steps that the macro needs to get done:

1. Assign a randomly generated number to each row of data in the master dataset, using RAND().
2. On a separate worksheet, calculate the number of TRX that needs to be randomly selected, for example, total number of TRX is 1724 rows, 30% is 517 rows of data.

    'Step 2 - Calculate the number of TRX to be randomly selected  (30% of Total Amount)
   
    Dim Num1 As Integer
    Dim Num2 As Single
   
    Dim CountaRange As Range
    Set CountaRange = Range("'Sales Data Master'!A2:A" & LastRow)
   
    Sheets("Random Pick Process").Select
    Range("G1").Value = WorksheetFunction.CountA(CountaRange)
   
    Num1 = Range("G1")
    Num2 = 0.3
    Range("H1").Formula = Num1 * Num2

3. Then taking step 2's Cell H1 value as the new "Last Row" value incorporate this last row value into a nested function of INDEX() & RANK() function which I am having trouble with..
I have tried the following code to create a nested function which works but really have no clue how I can autofill this line up to the "Last Row".

    v = Application.Index(CountaRange, Application.Rank(Range("'Sales Data Master'!Q2"), RandomRange), 1)
   
    With Application
    v = .Index(CountaRange, .Rank(Range("'Sales Data Master'!Q2"), RandomRange), 1)
End With

    Range("A3").Formula = v

Hoping to get some ideas to see if it's possible to incorporate the autofill element which stops at the "last row" in step 2 !

 

[bebo021999]

I cant understand 100% what you mean.
For example:
A1:A24 is 24 letters from A,B,C,...,Z
30% * 24 = 8
B1:B8 now is 8 random letters from A1:A24
Is it the case?
If yes, can the 8 random letters repeated, or must be unique?

 

[KosieNakata]

I cant understand 100% what you mean.
For example:
A1:A24 is 24 letters from A,B,C,...,Z
30% * 24 = 8
B1:B8 now is 8 random letters from A1:A24
Is it the case?
If yes, can the 8 random letters repeated, or must be unique?

Sorry for the confusion!

using your example to clarify.

A1:A24 is 24 letters from A-Z
The 1st step of the macro is to assign a random number to A1 to A24 using B1:B24, so each letter will have a random number that is used as an identifier for each letter.

Then the INDEX(...RANK(...) function will randomly choose a letter from the 24 letters (without duplication) on another spreadsheet (Refer to Image 1)

This " 30% * 24 = 8 " means that I only need to randomly choose a total of 8 letters from the 24 letters (no duplication allowed) and right now I'm just dragging the INDEX(...(RANK(...) formula down until it covers the necessary number of rows, in this case it's 8, however, in my real case...it could go up to thousands of rows.

That's why I'm hoping to be able to incorporate an autofill function based on the number 30% calculation (in this case 8).

will have to solve this part in order to move on

Thanks for your interest in advance Bebo!

 

[bebo021999]

With data are from A1
Results are from B1

Option Explicit
Sub randomlist()
Dim lr&, i&, rNum&, percent As Double, st As String, r As String, arr(), rng
lr = Cells(Rows.Count, "A").End(xlUp).Row
percent = 0.3 'adjustable
rNum = Int(lr * percent)
rng = Range("A1:A" & lr).Value
ReDim arr(1 To rNum, 1 To 1)
Randomize
    st = "-" & Int(rNum * Rnd + 1) 'genarate 1st random value
    For i = 1 To rNum - 1
        Do While InStr(1, st, r) > 0 'generate value until geting unique value
            r = "-" & Int(rNum * Rnd + 1)
        Loop
        st = st & r
    Next
    For i = 1 To rNum
        arr(i, 1) = rng(Split(st, "-")(i), 1)
    Next
Range("B1:B1000000").ClearContents
Range("B1").Resize(UBound(arr), 1).Value = arr
End Sub

 

[KosieNakata]

With data are from A1
Results are from B1

Option Explicit
Sub randomlist()
Dim lr&, i&, rNum&, percent As Double, st As String, r As String, arr(), rng
lr = Cells(Rows.Count, "A").End(xlUp).Row
percent = 0.3 'adjustable
rNum = Int(lr * percent)
rng = Range("A1:A" & lr).Value
ReDim arr(1 To rNum, 1 To 1)
Randomize
    st = "-" & Int(rNum * Rnd + 1) 'genarate 1st random value
    For i = 1 To rNum - 1
        Do While InStr(1, st, r) > 0 'generate value until geting unique value
            r = "-" & Int(rNum * Rnd + 1)
        Loop
        st = st & r
    Next
    For i = 1 To rNum
        arr(i, 1) = rng(Split(st, "-")(i), 1)
    Next
Range("B1:B1000000").ClearContents
Range("B1").Resize(UBound(arr), 1).Value = arr
End Sub

View attachment 68175

A bit overwhelmed with all the new functions here...going to try this out and come back with some questions! Thanks so much for your help Bebo!