Randomize Items in Drop Lists …With Every Use

Is it a New List? Randomizing Drop List with every use

Randomizing items in a drop list means every time you use the drop list the contents are the same but appear in a different order. This functionality is extremely user for collecting accurate answers from users whether it’s for a client feedback questionnaire, a test or quiz for school or college, may be also a survey to avoid robotic selection of options and in many other real work situations.

A close up of a logo  Description automatically generated

There are different ways of creating this functionality:

Method 1: Using a RAND() function and 2 helper columns

We have a list of phone types in Column F. So will be creating a corresponding list of random numbers in a helper column in column G using a RAND() function.

The RAND() function is an argument-less function that returns a decimal number between 0 and 1

A screenshot of a cell phone  Description automatically generated

These numbers change every time the worksheet recalculates or by hitting F9.

A picture containing bird  Description automatically generated

Next, we will be creating a second helper column (in Column D) that sorts the list of fruits based upon the random numbers in column G.

We will be using a dynamic array function to do that to ensure that sorting is recalculated when entering, changing or refreshing values in the worksheet.

The function we will use is the SORTBY function, and here are the arguments:

=SORTBY(array,by_array,sort_order,…)

Like any dynamic array function, the SORTBY function:

Requires using Office 365 (Now called Microsoft 365)

Is created once in cell D1

It spills to the cell below (unless there are contents)

Does not require locking the ranges used

=SORTBY(F1:F7,G1:G7,1)

F1:F7 ► is the range to sort and return

G1:G7 ► Is the range to base the sort upon

1 ► for ascending

A screenshot of a cell phone  Description automatically generated

Every time we hit F9 the function recalculates and returns a different order.

We are now ready to create our drop list in Cell B2. Select it and on the Data tab click on Data Validation.

A screenshot of a cell phone  Description automatically generated

Note: Alternatively, we can use the shortcut ALT + D + L

Under “Allow” ► Select List. In the source box we want to refer to the spilled array range, so we click on cell D1 and type #, which is the spilled array symbol ► Hit OK

=$D$1#

and we have the randomizing drop list. You can hide the helper columns if you want.

Method 2: Using RANDARRAY function

The previous method required creating 2 helper columns beside the original column with a total of 3. This method will use only one column (when completed).

We have a list of cell phones in column F and we want to ask users about their preferred phone type in a survey.

We start by creating a SORTBY function in cell D1, but for the second argument, we use a nested RANDARRAY function.

The RANDARRAY function is the one generating the randomizing numbers for the SORTBY. It has the following arguments:

=RANDARRAY(rows,columns,min,max,integer)

Rows ► the number of items we have in column F (8)

Columns ► We have one single column of items

Min ► The lowest number returned (I will set it to 1)

Max► The highest number returned (Select any number >=8)

=RANDARRAY(8,1,1,20)

Let’s put them together in cell D1:

=SORTBY(F1:F8,RANDARRAY(8,1,1,20),1)

The last “1” is for ascending sort.

A screenshot of a cell phone screen with text  Description automatically generated

The next step is to hard code the list of items (Cell phones) inside our function. To do that, put the function in the edit mode (hit F2) ► select the range F1:F8 ► hit F9. We see an array of values in curly brackets.

=SORTBY({“IPhone”;”Alcatel”;”Samsung”;”Sony”;”Huawei”;”LG Escape”;”Nokia”;”Ericsson”},RANDARRAY(8,1,1,20),1)

Instead of undoing (CTRL+ Z) as we usually do, hit “ENTER” to hard code the value

The function works as previous but it’s not relying anymore on column F

So, delete the values in column F and just keep one column of values returned by the nested functions.

A screenshot of a computer  Description automatically generated

We now have a single column to be used for our data validation drop list in cell B2. Every time we hit F9 the function recalculates and returns a different order.

With B2 selected, go to the Data tab click on Data Validation.

A screenshot of a cell phone  Description automatically generated

Alternatively, we can use the shortcut ALT > D > L (sequentially)  followed by TAB > L > TAB

Under “Allow” ► Select List. In the source box we want to refer to the spilled array range, so we click on cell D1 and type #, which is the spilled array symbol ► Hit OK

=$D$1#

and we have the randomizing drop list. You can hide the helper column if you want.

You can watch the video tutorial from my YouTube channel here below

 

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

SUBSCRIBE TO OUR EMAILING LIST AND RECEIVE FREE BOOK

Receive my Amazing Textbook “Pivot Tables From Zero To Hero” Totally FREE when you subscribe below to our emailing list.
SUBSCRIBE NOW
close-link