Question

excel - how to pick the whole row using randbetween

I'm trying to pick a random row from my dataset shown below.

I'm wanting to pick out 2 rows from the list at random, with column C being the column that is randomized and column D to match with the value show in column C.

Like this:

Dotty Davies | Kay, Changing Ends 1x03

Lucas Royalty | Young Jonah, 9-1-1 5x17

currently I'm using this formula

   =INDEX(C1:D10,RANDBETWEEN(1,ROWS(C1:C10)),1)
   =INDEX(D1:E10,RANDBETWEEN(1,ROWS(D1:D10)),1)

Which is showing this:

Dotty Davies | Redbird, Batwheels 2x10

Lucas Royalty | Self, Sesame Street

Column C appears to be working, however Column D isn't matching up with the original value in the row pulled from Column C

How do I do this correctly using Excel???

Dataset

C	D
Kylee Levien	Teen Autumn, Outer Range 2x06
Crew Kingston Miskel	Redbird, Batwheels 2x10
Lucas Royalty	Young Jonah, 9-1-1 5x17
Kiefer O'Reilly	Alexi Zinman, Alert: Missing Persons Unit 2x08
Oliver Savell	Young Alan Carr, Changing Ends 1x03
Dotty Davies	Kay, Changing Ends 1x03
Nariyah Ann SimpsonBoushee	Self, Sesame Street
Aiden Stoxx	Tayo Abiola, The Good Doctor 7x08
Charlie Storey	Young Sedona Jones, Sullivan's Crossing 2x02
Hannah Bos	Hannah Devlin, Dead Boy Detectives 1x03

2 67 2

1 Jan 1970

Solution

Try using the following formula:

=TAKE(SORTBY(C1:D10,RANDARRAY(ROWS(C1:D10))),2)

Shown demo in web version of Excel!

Or,

=CHOOSEROWS(SORT(C1:D10,RANDARRAY(ROWS(C1:D10)),1),SEQUENCE(2))

Using Structured References aka Tables:

=CHOOSEROWS(SORT(Table1,RANDARRAY(ROWS(Table1)),1),SEQUENCE(2))

2024-07-20

Mayukh Bhattacharya

Solution

Pick Random Rows

Input Sheet TV Actors: Convert List to Excel Table

Select the list (including headers).
Hold down Ctrl and press T and click OK.

Select any cell(s) in the table and select Table Design to rename it to e.g. TV_Actors.

Helper Sheet Hidden

In cell A2 enter the following spilling formula:
```
=SEQUENCE(ROWS(TV_Actors))
```
Hide it if preferable.

Formulas-->Defined Names-->Name Manager

Create the following names:

PickRandomRows

 =LAMBDA(data,rows,TAKE(SORTBY(data,RANDARRAY(ROWS(data))),rows))

TV_Actors_Rows
```
 =Hidden!A2#
```

Output Sheet Random

In cell A3, create a drop-down list using Data-->Data Tools-->Data Validation.
- Select List and use the following formula:
```
=TV_Actors_Rows
```

In cell A5 enter the following spilling formula:
```
=TV_Actors[#Headers]
```
In cell A6 enter the following spilling formula:
```
=PickRandomRows(TV_Actors,A3)
```
You can use the PickRandomRows (Lambda) function anywhere in your workbook.
Apply formatting as you see fit.
Play with it by selecting the number of rows in cell A3 and pressing F9 to recalculate.

2024-07-20

VBasic2008