Taking a big array and returning 50-100 random cells

[Hupo]

I've been using formulae like the following

=INDEX($A$1:$A$35,RANK(A40,$A$40:$A$74))
where A1 thru A35 are items I want in random order, and A40 thru A74 are all =RAND()

to get some random cells from a column/row but I have the following problems:

1. I don't understand how to make it work with an array, I have like 35 rows and 20 columns. I tried typing like =INDEX($A$1:$T$35 for that and it returns errors
2. Too much copy pasting and changing one letter to make it work by doing rows or columns separately, especially since I might be expanding the array sooner than later and would need to basically redo the whole thing


For example, I tried this smaller 5x5 version:

=INDEX($A$1:$E$5,RANK(A40,$A$40:$E$44))
(and expanded it to 5x5 cells)
By which I mean take cells of rows 1 thru 5 and columns A thru E, randomize the order using my =RAND() cells in rows 40 thru 44 and columns A thru E and return some random cells.

I'm starting to think the problem is the program not being able to rank and/or index if it's not a single list like a row or a column but more of a matrix? Or am I doing something wrong?
If there's a completely different way to do this, I'm open to it, I just have a 35*20 cells and want to take a random sample of 50-100 of them, returned as another array of cells below it. (i.e. not like a randomized list of numbers in one cell)

 

[Domenic]

For simplicity sake, let's say that A2:C4 contains your data, and that you want to return 5 random values from that range...

1) Enter the following formula in E2, copy across to G2, and copy down to Row 4...

=RAND()

2) Select cell I2, and define the following names (Ribbon >> Formulas >> Defined Names >> Name Manager)...

Name:  NumberToRank

Refers to:  =INDEX('Sheet1'!$E$2:$G$4,INT((ROWS('Sheet1'!$I$2:I2)-1)/COLUMNS('Sheet1'!$E$2:$G$4))+1,MOD(ROWS('Sheet1'!$I$2:I2)-1,COLUMNS('Sheet1'!$E$2:$G$4))+1)

Name:  Ranking

Refers to:  =RANK(NumberToRank,'Sheet1'!$E$2:$G$4)

3) Enter the followng formula in I2, and copy down:

=INDEX($A$2:$C$4,INT((Ranking-1)/COLUMNS($A$2:$C$4))+1,MOD(Ranking-1,COLUMNS($A$2:$C$4))+1)

Here's the result...

hupo.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1									Return 5 random values
2	100	200	300		0.429673	0.435042	0.251403		300
3	400	500	600		0.000288	0.807129	0.001372		200
4	700	800	900		0.300568	0.265404	0.369764		700
5									900
6									100
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=RAND()
I2:I6	I2	=INDEX($A$2:$C$4,INT((Ranking-1)/COLUMNS($A$2:$C$4))+1,MOD(Ranking-1,COLUMNS($A$2:$C$4))+1)

Named Ranges
Name	Refers To	Cells
NumberToRank	=INDEX(Sheet1!$E$2:$G$4,INT((ROWS(Sheet1!$I$2:I6)-1)/COLUMNS(Sheet1!$E$2:$G$4))+1,MOD(ROWS(Sheet1!$I$2:I6)-1,COLUMNS(Sheet1!$E$2:$G$4))+1)	I2:I6

Hope this helps!

 

[Hupo]

Yeah, thanks a lot! I still couldn't get it to do the outputs as an array (it appears like the same column over and over) but I suppose I can just print the randoms in one column and then link to those cells in an array