Taking a big array and returning 50-100 random cells

Hupo

New Member
Joined
Aug 20, 2022
Messages
2
Office Version
  1. 2021
  2. 2013
Platform
  1. Windows
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)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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...

Excel Formula:
=RAND()

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

Excel Formula:
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)

Excel Formula:
Name:  Ranking

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

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

Excel Formula:
=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
ABCDEFGHIJK
1Return 5 random values
21002003000.4296730.4350420.251403300
34005006000.0002880.8071290.001372200
47008009000.3005680.2654040.369764700
5900
6100
7
8
9
10
Sheet1
Cell Formulas
RangeFormula
E2:G4E2=RAND()
I2:I6I2=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
NameRefers ToCells
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!
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top