Some smart way to pick 100k of 103k numbers randomly?

[Jaymond Flurrie]

I have an array of 103 000 numbers. I would need to randomly pick 100 000 of them. Anyone here has a smart and relatively fast way to do it? Or alternatively, is there some "SELECT RANDOM"-clause in SQL?

 

[mirabeau]

You might try a VBA code.

Numbers taken to be listed down Column A, although you can generate your array in other ways if you like.

Maybe this code should be fast enough for you.

Sub randselect()
Dim a As Variant, n As Long, i As Long
Dim x As Variant, u() As Variant
a = [a1].CurrentRegion.Resize(, 1)
n = UBound(a)
ReDim u(1 To n, 1 To 1)
Randomize
For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    u(i, 1) = a(x, 1)
    a(x, 1) = a(i, 1)
Next i
If n > 10 ^ 5 Then n = 10 ^ 5
[c1].Resize(n) = u
[c1].Resize(n).Insert xlToRight
End Sub

 

[Ruddles]

Actually an array or are the numbers in a worksheet?

If the latter, generate random numbers in the column next to the values you want to select from, then sort both columns using the random numbers as the sort key. Then use the first 100k of your values.

You need only generate the random numbers once since recalculating the sheet will recalculate them all again.

 

[Jaymond Flurrie]

Actually an array or are the numbers in a worksheet?

If the latter, generate random numbers in the column next to the values you want to select from, then sort both columns using the random numbers as the sort key. Then use the first 100k of your values.

You need only generate the random numbers once since recalculating the sheet will recalculate them all again.

An array that I receive from a SQL query against an external worksheet.

I also realized that if you need 100 000 of 103 000, it might be smarter to randomize those 3 000 and then use everything but them. Although here it would make quite a bit of work, but basically that should work.

 

[mirabeau]

Jaymond,

The code I gave above will do what you requested on any single-column array whether taken from a worksheet, an SQL query or wherever you like, some 4 times faster than the helper column/sort approach which only works on worksheets (and with considerable fiddling if you do it manually).

From your comments, I guess you didn't try it.

However, the problem is of course yours, not mine, so ...