Random number pick from array

[Necroink]

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

 

[jasonb75]

If you just want random integers from a list of consecutive numbers then you could use

=RANDBETWEEN(1,51)

in 3 cells.

For a random selection from a list of unknown numbers, something like

=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]

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

 

[jasonb75]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
2	1	2	3	4	5	6	7	8	9	10		0.253488459
3	11	13	14	15	16	17	18	20				0.284297321
4	21	22	23	24	26	27	28	29	30			0.445186068
5	31	32	33	34	35	36	38	39	40			0.128231027
6	41	42	43	44	45	46	47	48	49	50		0.733487221
7	51											0.710043975
8												0.154434564
9	3											0.30285558
10												0.738047228
11	31		38									0.340228367
12	18		35									0.224572755
13	7		26									0.332601358
14												0.846643922
Sheet3

Cell Formulas
Range	Formula
A11:A14	A11	=IF(ROWS(A$11:A11)>$A$9,"",LARGE($A$2:$J$7,RANDBETWEEN(1,COUNT($A$2:$J$7))))
C11:C14	C11	=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:L14	L2	=RAND()

 

[Necroink]

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
	A	B	C	D	E	F	G	H	I	J	K	L
2	1	2	3	4	5	6	7	8	9	10		0.253488459
3	11	13	14	15	16	17	18	20				0.284297321
4	21	22	23	24	26	27	28	29	30			0.445186068
5	31	32	33	34	35	36	38	39	40			0.128231027
6	41	42	43	44	45	46	47	48	49	50		0.733487221
7	51											0.710043975
8												0.154434564
9	3											0.30285558
10												0.738047228
11	31		38									0.340228367
12	18		35									0.224572755
13	7		26									0.332601358
14												0.846643922
Sheet3

Cell Formulas
Range	Formula
A11:A14	A11	=IF(ROWS(A$11:A11)>$A$9,"",LARGE($A$2:$J$7,RANDBETWEEN(1,COUNT($A$2:$J$7))))
C11:C14	C11	=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:L14	L2	=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]

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.