Random sampling with criteria

[SerenityNetworks]

I have a column with 27 unique names (criteria). There are tens of thousands of rows for each name. Of course, each name has adjoining columns with other data. I need to randomly select 200 rows for each name. What would be the best or easiest method to go about doing so?

Thanks in advance,
Andrew

 

[NiMip]

I'm struggling for ideas, but this is one way I can think of using a simple filter on the columns.

If you filter on the Rank column to remove blanks, it will show a random 10 entries for Greg. When you actually apply the filter, the values in the Rank column will recalculate and all be out of whack, but the important thing is that there's as many as you've specified, and they're randomly selected. Every time you reapply the filter, it will display a new random set of entries. You can change which name and how many you want it to show by changing the values in B1 and B2, respectively.

If you want some kind of summary sheet of a random selection for each name, I'll have to come up with something a bit different.

[TABLE="class: grid, width: 432"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD]Greg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample Size:[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Rand number[/TD]
[TD]Rank[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]=IF(A5=$B$1,RAND(),"")[/TD]
[TD]=IFERROR(IF(RANK(B5,$B$5:$B$40)>$B$2,"",RANK(B5,$B$5:$B$40)),"")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.589777001[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.648446673[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.18382557[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.597472993[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.793071478[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.768041721[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.220654573[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.52447789[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.154778035[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.757042513[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.969265735[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]0.163463269[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SerenityNetworks]

Thank you. I made a variation that worked better for my data, but I used your strategy entirely. I was kind of hoping I could do it in just one formula, perhaps a UDF, but since I only have to do it once this worked just fine.

Thank you again,
Andrew

 

[NiMip]

No worries, it was an interesting problem to solve and I'm glad I could help! Even if it is a bit clunky haha. I guess for a once-off, sometimes simple is best.