arthurklein43
New Member
- Joined
- Aug 3, 2018
- Messages
- 15
Hello again
I wasted all day trying to resolve this problem without help but unfortunately unsuccessful. so here I am asking for help the great helpful community here.
I have a column A with 10K numbers in ascending order and could be even more . I need to pick 500 random unique numbers from that column and display them in separate column. order doesnt matter . just need to be random unique and mixed so i guess they shouldn be in any order just mixed.
here is what i tried so far
I found a post from Eric W post #2 here https://www.mrexcel.com/forum/excel-questions/937500-vba-unique-random-numbers-list.html?highlight=pick+random+numbers+column
Array Formulas
<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
<tbody>
</tbody>I used the formula in H8 and it works great but the problem is it is good for 20-30-50 numbers but for more than that slows down the excel.It takes a while to do the calculation. also the other problem was that if i do anything in worksheet like add a number in different cell or even just copy or paste something it refresh the whole worksheet and the formulas start recalculating and change the numbers again and recalculating takes a while . even if I use another worksheet it still recalculating. I need them if possible to stay still so i can work with them.
So practically I need a simple formula that will not slow down the calculation when in use with large amount of numbers. numbers are 12-13 digits each.
I think I covered everything. Thanks everyone for your help
I wasted all day trying to resolve this problem without help but unfortunately unsuccessful. so here I am asking for help the great helpful community here.
I have a column A with 10K numbers in ascending order and could be even more . I need to pick 500 random unique numbers from that column and display them in separate column. order doesnt matter . just need to be random unique and mixed so i guess they shouldn be in any order just mixed.
here is what i tried so far
I found a post from Eric W post #2 here https://www.mrexcel.com/forum/excel-questions/937500-vba-unique-random-numbers-list.html?highlight=pick+random+numbers+column
Sheet2
Array Formulas
Cell | Formula |
---|---|
H8 | {=LARGE(IF((COUNTIF($H$7:H7, $E$8:$E$100)=0)*($E$8:$E$100<>""),$E$8:$E$100), RANDBETWEEN(1,SUM(IF((COUNTIF($H$7:H7, $E$8:$E$100)=0)*($E$8:$E$100),1))))} |
<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
E | F | G | H | I | J | ||
---|---|---|---|---|---|---|---|
7 | Random selection | ||||||
8 | 1 | 22 | |||||
9 | 2 | 18 | |||||
10 | 3 | 21 | |||||
11 | 4 | 10 | |||||
12 | 5 | 14 | |||||
13 | 6 | 4 | |||||
14 | 7 | 9 | |||||
15 | 8 | 2 | |||||
16 | 9 | 13 | |||||
17 | 10 | 15 | |||||
18 | 11 | ||||||
19 | 12 | ||||||
20 | 13 | ||||||
21 | 14 | ||||||
22 | 15 | ||||||
23 | 16 | ||||||
24 | 17 | ||||||
25 | 18 | ||||||
26 | 19 | ||||||
27 | 20 | ||||||
28 | 21 | ||||||
29 | 22 | ||||||
30 |
<tbody>
</tbody>
So practically I need a simple formula that will not slow down the calculation when in use with large amount of numbers. numbers are 12-13 digits each.
I think I covered everything. Thanks everyone for your help