Randon Selection of Items within Worksheet

[Dolly]

Hi,

I am trying to find the process of taking a spreadsheet (worksheet) and
having the system randomly select 10% of items >$10,000 (or can be <10.000).
This process will save time when there are a lot of items listed within the
spreadsheet and also prevent "favorite pickings" manually.

Any idea how I can implement this process? Thank you!

 

[Gary''s Student]

Easy with some "helper columns. Say in B1 thru B50 we have data like:

10331
10090
9934
9973
10208
9755
9508
10425
9861
10393
10363
9879
9820
10438
10019
10357
10367
10072
9757
10032
9550
9657
9816
9608
9579
10332
10096
9984
10189
10062
10434
10392
9834
9505
9797
10164
9928
10013
10401
9515
10084
9516
9578
10397
10042
10035
10258
10141
10450
10137
Some are above 10,000 and some are below. In A1 enter:
=IF(B1>10000,1,"")
in A2 enter:
=IF(B2>10000,MAX($A$1:A1)+1,"") and copy down. in cols A & B we see:
1 10331
2 10090
9934
9973
3 10208
9755
9508
4 10425
9861
5 10393
6 10363
9879
9820
7 10438
8 10019
9 10357
10 10367
11 10072
9757
12 10032
9550
9657
9816
9608
9579
13 10332
14 10096
9984
15 10189
16 10062
17 10434
18 10392
9834
9505
9797
19 10164
9928
20 10013
21 10401
9515
22 10084
9516
9578
23 10397
24 10042
25 10035
26 10258
27 10141
28 10450
29 10137

So each value above 10,000 is assigned a unique id.

In D1 enter:
=VLOOKUP(ROW(),$A$1:$B$50,2,FALSE) and copy down
This produces a compact list of the values above 10,000
In C1 thru C29, enter:
=RAND()

We now have:
1 10331 0.683103015 10331
2 10090 0.048756648 10090
9934 0.935049477 10208
9973 0.242085647 10425
3 10208 0.250073799 10393
9755 0.080849429 10363
9508 0.600361089 10438
4 10425 0.446171341 10019
9861 0.974372046 10357
5 10393 0.728390252 10367
6 10363 0.676991942 10072
9879 0.751048201 10032
9820 0.487372936 10332
7 10438 0.805150858 10096
8 10019 0.12937254 10189
9 10357 0.148004206 10062
10 10367 0.492633818 10434
11 10072 0.951275211 10392
9757 0.131207134 10164
12 10032 0.514146927 10013
9550 0.887885183 10401
9657 0.622394419 10084
9816 0.151118334 10397
9608 0.19107921 10042
9579 0.640390167 10035
13 10332 0.7436116 10258
14 10096 0.953181617 10141
9984 0.980316755 10450
15 10189 0.956090487 10137
16 10062 #N/A
17 10434 #N/A
18 10392 #N/A
9834 #N/A
9505 #N/A
9797 #N/A
19 10164 #N/A
9928 #N/A
20 10013 #N/A
21 10401 #N/A
9515 #N/A
22 10084 #N/A
9516 #N/A
9578 #N/A
23 10397 #N/A
24 10042 #N/A
25 10035 #N/A
26 10258 #N/A
27 10141 #N/A
28 10450 #N/A
29 10137 #N/A



Finally, in E1 thru E5 enter:
=VLOOKUP(LARGE($C$1:$C$29,ROW()),$C$1:$D$29,2,FALSE)
We see in E1 thru E5:

10137
10164
10032
10392
10401

These are 5 random samples of the data in column D

 

[Dolly]

Thanks.

Next question - would the same process work in Vector?