# Random number pick from array

#### Necroink

##### New Member
Hi Excel Guru's

After checking numerous threads i can fine solution to my problem.

I have an array of numbers and need to pick a certain (variable) amount from that array, see below

1 2 3 4 5 6 7 8 9 10
11 13 14 15 16 17 18 20
21 22 23 24 26 27 28 29 30
31 32 33 34 35 36 38 39 40
41 42 43 44 45 46 47 48 49 50
51

Numbers
3

results

the "numbers" cell is the amount of random numbers i want from the array, some cells will be empty and so skipp those cells and return set numbers in results cells

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### jasonb75

##### Well-known Member
If you just want random integers from a list of consecutive numbers then you could use
Excel Formula:
``=RANDBETWEEN(1,51)``
in 3 cells.

For a random selection from a list of unknown numbers, something like
Excel Formula:
``=LARGE(array,RANDBETWEEN(1,COUNT(array))``

It is possible that you could get duplicate values returned, if that is the case you can recalculate by pressing f9 to try again.

There are more complex methods that can be used to prevent duplicates from occurring if needed.

#### Necroink

##### New Member
Thanks, however those solutions i have tried and they dont quite work for what i need

#### jasonb75

##### Well-known Member
they dont quite work for what i need
Could you be a bit more specific as to why they are not suitable so that we can eliminate any other methods that might not work for the same reasons.

#### Necroink

##### New Member

Could you be a bit more specific as to why they are not suitable so that we can eliminate any other methods that might not work for the same reasons.
they it counts blank spaces and creates duplicates, also tried simalar formalaes from other sites, i need it to look at the cells, ignore blanks/0's , i also want it to pull a set number from an input source

#### jasonb75

##### Well-known Member
Both would ignore blanks. There was no mention of ignoring 0's, if that is a requirement then it will add to the complication.

The formula in column A below uses the second formula that I suggested previously, with an addition to allow for the number of values to be returned. The formula needs to be filled down adequately, as you are using excel 2019 you do not have the option of dynamic arrays.

Although it is possible the second method in column C is extremely unlikely to produce duplicates, however it does require a helper array (column L in the example) which must be a single row or column equal in size to the number of cells in the main array. I've only included the top part below, the formula is filled down to L61.

Any formula methods for what you are trying to do would be similar to one of these methods, anything without the additional column will mean a higher possibility of duplicates. If neither are suitable then the next step will be vba.

Book1 (version 1).xlsb
ABCDEFGHIJKL
2123456789100.253488459
311131415161718200.284297321
42122232426272829300.445186068
53132333435363839400.128231027
6414243444546474849500.733487221
7510.710043975
80.154434564
930.30285558
100.738047228
1131380.340228367
1218350.224572755
137260.332601358
14  0.846643922
Sheet3
Cell Formulas
RangeFormula
A11:A14A11=IF(ROWS(A\$11:A11)>\$A\$9,"",LARGE(\$A\$2:\$J\$7,RANDBETWEEN(1,COUNT(\$A\$2:\$J\$7))))
C11:C14C11=IF(ROWS(A\$11:A11)>\$A\$9,"",SMALL(\$A\$2:\$J\$7,RANK(L2,\$L\$2:INDEX(\$L\$2:\$L\$61,COUNT(\$A\$2:\$J\$7)))))
L2:L14L2=RAND()

#### Necroink

##### New Member
Both would ignore blanks. There was no mention of ignoring 0's, if that is a requirement then it will add to the complication.

The formula in column A below uses the second formula that I suggested previously, with an addition to allow for the number of values to be returned. The formula needs to be filled down adequately, as you are using excel 2019 you do not have the option of dynamic arrays.

Although it is possible the second method in column C is extremely unlikely to produce duplicates, however it does require a helper array (column L in the example) which must be a single row or column equal in size to the number of cells in the main array. I've only included the top part below, the formula is filled down to L61.

Any formula methods for what you are trying to do would be similar to one of these methods, anything without the additional column will mean a higher possibility of duplicates. If neither are suitable then the next step will be vba.

Book1 (version 1).xlsb
ABCDEFGHIJKL
2123456789100.253488459
311131415161718200.284297321
42122232426272829300.445186068
53132333435363839400.128231027
6414243444546474849500.733487221
7510.710043975
80.154434564
930.30285558
100.738047228
1131380.340228367
1218350.224572755
137260.332601358
14  0.846643922
Sheet3
Cell Formulas
RangeFormula
A11:A14A11=IF(ROWS(A\$11:A11)>\$A\$9,"",LARGE(\$A\$2:\$J\$7,RANDBETWEEN(1,COUNT(\$A\$2:\$J\$7))))
C11:C14C11=IF(ROWS(A\$11:A11)>\$A\$9,"",SMALL(\$A\$2:\$J\$7,RANK(L2,\$L\$2:INDEX(\$L\$2:\$L\$61,COUNT(\$A\$2:\$J\$7)))))
L2:L14L2=RAND()
i do appreciate you asssiting me, i think VBA would be a better option , as i cant have duplicates or a zero come up due to blank cells , i will try what you have posted and come back to you

#### jasonb75

##### Well-known Member
a zero come up due to blank cells
The formulas that I've used will only return a zero if there is an actual zero in the array. Empty cells are ignored completely.

Replies
2
Views
235
Replies
15
Views
113
Replies
1
Views
152
Replies
0
Views
195
Replies
4
Views
275

1,127,610
Messages
5,625,795
Members
416,138
Latest member
Pizzaman22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back