| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randon Selection of Items within Worksheet
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Randon Selection of Items within Worksheet
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(B110000,1,"") in A2 enter: =IF(B210000,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,FALS E) We see in E1 thru E5: 10137 10164 10032 10392 10401 These are 5 random samples of the data in column D -- Gary''s Student - gsnu201001 "Dolly" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Randon Selection of Items within Worksheet
Thanks.
Next question - would the same process work in Vector? "Gary''s Student" wrote: 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(B110000,1,"") in A2 enter: =IF(B210000,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,FALS E) We see in E1 thru E5: 10137 10164 10032 10392 10401 These are 5 random samples of the data in column D -- Gary''s Student - gsnu201001 "Dolly" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Randon selection of a value | Excel Discussion (Misc queries) | |||
| Drop Down - Multiple Items Selection | Excel Discussion (Misc queries) | |||
| Displaying Drop down list items based on selection of another colu | Excel Discussion (Misc queries) | |||
| Using Query for a selection of items | Excel Discussion (Misc queries) | |||
| Random Selection of items in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
