Random Sampling 10% of Data

[geralde]

Hi All

I have a 290,000 rows of data and I want to randomly pick a sample of 10% of that data.

Can you please help me with a formula to do this?

Thanks

Gerald

 

[JEC]

VBA solution as well:

Sub jec()
 sp = Range("A2", Range("A" & Rows.Count).End(xlUp))
 sp2 = Application.RandArray(UBound(sp), 1)
 ReDim ar(1 To UBound(sp), 1 To 2)
 
 For j = 1 To (UBound(sp) * 0.1)
    ar(j, 1) = sp(Application.Match(Application.Large(sp2, j), sp2, 0), 1)
    ar(j, 2) = j
 Next

 Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1).Resize(10) = ar
End Sub

 

[geralde]

VBA solution as well:

Sub jec()
 sp = Range("A2", Range("A" & Rows.Count).End(xlUp))
 sp2 = Application.RandArray(UBound(sp), 1)
 ReDim ar(1 To UBound(sp), 1 To 2)
 
 For j = 1 To (UBound(sp) * 0.1)
    ar(j, 1) = sp(Application.Match(Application.Large(sp2, j), sp2, 0), 1)
    ar(j, 2) = j
 Next

 Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1).Resize(10) = ar
End Sub

thank you so much. My VBA is truly awful henxe the reason I stick to excel

 

[JEC]

But, if you use the formula version, you should set your calculation settings to manual. Otherwise the formula is recalculating every time you make a change in your worksheet.

 

[Fluff]

Or just copy/paste values to another part of the sheet.

 

[geralde]

But, if you use the formula version, you should set your calculation settings to manual. Otherwise the formula is recalculating every time you make a change in your worksheet.

thank you much for this