Instructor: All right, buckle up. It is pro tip time.
This is an advanced one.
We're gonna talk about creating random, top-end lists
using the RANDARRAY function.
But the reason it's advanced
is that we're not just using RANDARRAY.
We're gonna combine RANDARRAY
with a bunch of functions we've already learned.
We're gonna use Unique, we're gonna use Sequence,
we're gonna use Sort By,
we'll even use functions like Index and Rows as well.
The good news is that I'm gonna break this process down,
piece by piece, step-by-step,
so that you can see exactly how this is all working.
So if you're brave enough to follow along,
jump into your Dynamic Array formula workbook
and head right to the RANDARRAY tab.
So here's the data that we're used to working with.
We've got industries, jobs, cities, salaries,
and year-over-year growth rates.
And what we're gonna do
is basically follow four steps, right?
We're gonna start by returning a unique list of jobs.
Easy enough.
Then we're gonna randomize that list of jobs.
Then we're gonna pull out one of those random jobs,
then make that a bit more dynamic
and pull out the top-end random jobs
from that randomized list.
Now, as if that weren't enough,
the final step will actually be to combine
all of those steps together into one single formula,
which we'll populate right here in Column Q.
So without further ado,
let's go ahead and give this thing a shot,
starting with our unique list of jobs.
So this is step one, pretty straightforward.
We're gonna use our handy Unique function.
We're gonna point to our job values right here
in B-three through B-842.
Close the parenthesis, press Enter. And there you have it.
We've got our list of unique job titles.
And if we select all of them,
you can see we've got 84 titles represented here.
So step one, complete, easy enough.
Step two is to randomize that list of jobs
that we just created.
So to randomize it, we know we need to resort that list.
But we're not sorting by any field that we have,
or in any logical way, right?
We're not sorting alphabetical.
We're not sorting by salary or growth rate or anything else.
So we know we need to use a Sort By,
'cause we're sorting by a different array of values
that doesn't exist as a field in our array.
So let's start there.
We do Sort By.
And the array that we're sorting is that spill range
that we just created, that unique list of jobs,
G-three pound.
And here's where it gets a little bit tricky, right?
How are we sorting those titles?
What's the by array parameter in this formula?
And when you think about it,
if we could create an array of random numbers
that's 84 rows tall and sort by that random set of values,
that would essentially allow us
to randomize the titles themselves.
So the best way to do that
is to actually use a rand array function
right here in this parameter argument.
And the number of rows, remember,
it's gotta be the same size array
as our spill range in G-three,
which in this case we know is 84 rows tall.
So I could just hard code the 84, type it in,
close it off with two parentheses,
and press Enter and check it out, that actually works.
It gave me a randomized list of job titles.
It's the exact same size as my original spill range,
84 rows tall.
But obviously we want to avoid hard coding values
in case anything changes in the future.
So instead of the 84,
what we can do is tell Excel to return the number of rows
in that spill range.
So we can actually use the rows function,
point to that same spill range, G-three pound.
We're gonna need to close one more parenthesis
and press Enter.
So there you go, same results. Now it's just dynamic.
And if we want to see this randomized list change,
you can use the F-nine shortcut to recalculate the formula,
or you can navigate to the formulas tab
and click Calculate Now.
Same exact thing. So that's step two, all done.
Moving on to step three,
we want to extract just one random job title
from our original list.
Well, this one's actually pretty easy,
because our spill range in Column I is random.
All we need to do is just pull,
let's say the top job from that list.
So to do that, we can use an index function,
which we've learned.
Our array is that spill range, I-three pound.
And in this case, since we're just grabbing one title,
you can just grab the first row,
or the first value in that array, close it off, press Enter.
And there we go.
Teacher, teacher, project manager, bank teller,
producer, physical therapist.
That looks good. Step three, done.
Now moving on to step four.
Let's apply that exact same logic,
but instead of retrieving just one random job,
let's return N random jobs,
based on whatever value is populated here in cell M-two.
So in this case, we want to return five random jobs,
instead of one.
We can start exactly the same way,
by indexing our spill range, I-three pound.
And now instead of our row number of one or two or fixed
at any value, we need to make it dynamic.
And in this case,
since we have a five populated in cell M two,
we're essentially asking Excel to return the first job
in that list, then the second, then the third,
then the fourth and then the fifth.
So we want to produce an array that's five rows tall
by inserting a formula argument or parameter
that's essentially a sequence of those first five numbers.
One, two, three, four, five.
So I gave you a little bit of a hint there.
The way we can make this row number argument dynamic
is to use our trusty sequence formula.
And we can point right to that cell M-two,
which in this case is five.
That's gonna give us a sequence five rows tall,
starting with one with a step value of one,
which are the default parameters.
Close it off with two parentheses, press Enter.
And there you go.
Now we've got our top N, in this case, our top five jobs.
You can randomize it.
And we'll see that list of five update
each time we recalculate.
So congratulations,
we've made it through each of the four steps
to produce a random top-end job title generator.
Now the final step, which is pretty tricky,
is to combine all of that into one single formula
in Column Q.
And the way I like to do this
is essentially to work backwards.
'Cause each of these formula's
kind of built on each other, right?
And if we can start from the outside in,
we know we wanna start with an index function.
We know our index function is referencing our spill range
in Column I.
We know that Column I is a Sort By function
that's referencing our spill range in Column G.
And we know our spill range in Column G
is a unique function referencing our raw data.
Since we have all of those pieces,
now it's just a function of assembling them all together.
So let's go ahead and start
by copying that index function we have.
And oops, let's press Escape. Escape again.
And let's paste the index function right here.
We can press Enter.
You can see we got the same results as Column N,
but now instead of referencing our spill range in I-three,
I'm gonna jump to I-three,
grab that entire function, everything after the equal sign,
Control-C to copy it, press Enter,
go back into my Q-three function.
I'm gonna literally just Control-V
to paste that whole function in place of my spill range.
Press Enter again.
And then final step,
anywhere that I'm referencing my G-three spill range,
I'm gonna grab that actual formula for that spill range
and input that in its place.
So final step, Control, copy this unique function,
head back to our formula.
And we have two instances of that G-three spill range
that we need to replace.
So I'm gonna paste that here. I'm gonna paste that here.
And why don't we actually just reference instead of M-two,
let's reference P-two here,
which is the number of random titles we want to return.
And then press Enter.
So it's working. It looks great.
And notice that now we're not referencing
any of those preliminary steps or helper columns
that we created.
We're only referencing the raw source data itself.
So the ultimate test here
would be to actually delete all of these intermediate steps.
Boom, and see if it still works.
F-nine, F-nine, looks perfect.
We just accomplished the exact same thing
using this one nested function
instead of those individual steps.
So I'm gonna Control-Z 'cause I want to see those steps.
It can be helpful as a learning tool
to see basically the pieces or components of that function.
But there you have it.
Really nice, advanced example,
of how you can combine these
different dynamic array functions,
along with other legacy Excel functions
to do some really powerful, really unique things,
that in the past,
would've been really, really difficult to do in Excel.
So there you have it. That's our pro tip.
Creating random top-end lists with RANDARRAY.