Random number pick from array

Necroink

New Member
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,459
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Thanks, however those solutions i have tried and they dont quite work for what i need
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,459
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,459
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,459
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top