Today's topic is to extract unique values
from a list of data, and we're going to do this with formulas.
In the first section of the video, I'm going to show you
how you can create a list of unique items.
And in the second part of the video I'm going to show you
how you can create a dynamic drop-down list
that only shows these unique items.
Now in my quest to find the proper formula for this,
I came across simpler versions that required CSE,
so Control + Shift + Enter, but I really wanted to come up
with a formula that didn't require CSE,
this is what I came up with.
Here I have a table with the different divisions
and the apps that belong to the division, my aim is to get
a unique list of divisions here.
And later based on this unique list I'm going to create
a dynamic drop-down that references this list.
And I want this to be totally dynamic, so the moment
I add another division within here this gets updated,
the drop-down is updated.
And also if I add a new division right to the bottom,
everything gets updated.
So that's already the first clue that
I should probably translate this transferred as
into an official Excel table.
So I'm going to do that first, just click anywhere inside
and press Control + T.
Let's say okay, the table has headers, now I'm going to go
and clear the style, and while I'm here let me give it
a name, I'm going to call it TableDiv.
Now I'm going to get my unique list in here,
what formula could I use?
Now one thing that came to my mind is the INDEX formula,
because what I could do is to INDEX this area
and basically tell it how much to go down to get
to the next value that hasn't occurred on my list yet.
If I just start just so that we have an idea
of where we going.
If I start with INDEX and give it the array
is basically the area where my answer is, well my answer
is going to be a division.
Now I need to tell it how many rows to go down.
Well, the first occurrence is going to be unique, right?
Because I don't have anything on my list yet,
so that's going to be one.
But when I pull this down, I need this one
to become a four, right?
To give me a back game, because game hasn't occurred yet,
productivity has already occurred here.
And when I pull this down even further I need to get utility
so which position is that?
It looks like the seventh position, right?
So somehow I need to get these numbers dynamic,
and somehow I need to connect this list,
basically the occurrences that have already happened
with this and I have to compare them to one another.
So I have to look and see well, have you already
found productivity there, which positions
are productivity in this range, and ignore them.
And when I drag this down here I need to take a look back
at game and productivity and look back here and say,
which positions have game and productivity occurred,
and cancel those positions out, so I get to the cell
that I'm interested, so that the one
that hasn't occurred yet on this list.
Okay, and the key is to come up with these numbers.
So let's say I managed to find productivity because well,
that's the first occurrence that's going to be unique,
how do I find that four here?
Okay, so I'm just going to type the formula in here first.
One formula that occurs to me is the COUNTIF function.
Now we're normally used to seeing COUNTIF in this way,
that we look at a range, so let's say I just pick this range
until the row 10, and we look at a criteria.
So I look at productivity, what answer would I get now?
Five, right, how many times does productivity occurred
in here, but what if I turn this around?
What if they change this instead of my range,
I put my criteria in there, and instead of my criteria
I put my range in there, and I press Enter?
It looks like I get one answer, but in fact
I get many answers, let me just press F9
and we can see that here.
Because I've switched them around I'm getting back an array
and you can see why the ones happen,
that's when the MATCH was on.
So the first position is for the activity, second yes, yes
and here it didn't find productivity.
So I can actually use this information
and find the first zero in this list.
So I'm just going to press Control + Z to go back.
Now a function that's common to use with INDEX
and I use it a lot is the MATCH function.
And now our lookup value is the zero,
and our lookup array is this array right here,
and our MATCH type is zero because we want to find
a perfect MATCH.
But now when I press Enter, it doesn't work, right?
Because match is not programmed to be an array function,
to memorize all of this in memory.
I have to press Control + Shift + Enter
to convert it into an array, and here you can see
I get back the four.
But I said originally that I'm looking for a function
that does not require Control + Shift + Enter,
so what I'm going to do is to wrap this part
that MATCH can't handle as an array
without pressing Control + Shift + Enter,
I'm going to wrap it up inside an INDEX function,
because INDEX is already
a programmed Control + Shift + Enter function.
So I'm going to use it to carry this part
that MATCH can't handle.
In the INDEX function I have an array
that's basically the map that I need, but both the row
and the column are irrelevant here, right?
Because I do want every single row,
I do want every single column but the rule argument
is mandatory INDEX.
So I just have to put the Excel separator to acknowledge
that I know that it's mandatory but I don't need to give
it any arguments and I'm going to close the bracket right here.
Okay, so now I'm just going to press Enter
and I get back the four, right?
That's the exact four that I need to get game back,
but what happens when I pull this down?
I don't get what I need here, right?
I need a seven I get a one, why?
First of all let me put my INDEX range back up,
but even if I press ENTER now I still get one, why?
Because I'm only looking at game, so the game part
is the fourth position but I also need to take into account
that I have productivity as well.
So I just need to expand this area, when I press Enter
I get the seven that I need.
So now I know how to update this formula
so that it's dynamic and I just need to write it
once properly and pull it down.
Okay, so let's start from here, we wrote the INDEX part
that's fine, this is the part that we need automatic
and now let's reference the entire table instead
of a section of a table.
So I'm going to MATCH, my lookup value was zero, right?
Because I'm creating my own array for it to look up,
and my array was COUNTIF, my range is basically the cell
above me to itself, and I need
to fix the first cell reference.
Next the criteria is my table, so I'm going to reference this,
okay and that's my COUNTIF part then I want a perfect MATCH,
and that would be the end of my INDEX.
If I was going the CSE way,
so if I just press Control + Shift + Enter
and see I get a value, that's great,
but because I want to avoid that I'm going to put this part
of the formula inside my INDEX, and right here I need
to add the Excel separator for the row argument
which is mandatory, close the bracket and press Enter.
Now when I pull this down, I get the unique divisions
in here and a bunch of errors which I'm going to get rid of
by putting it in the IFERROR function.
Okay, otherwise give back nothing
and I'm going to pull it up to here.
Okay, so this looks good, let's just double check,
I'm going to put new here and I get it here,
let's do the next test I'm going to put Leila division here
and we get it in here as well.
Now that we have created a dynamic list
that gives us unique divisions,
we're going to create our drop-down.
Now obviously I can just go here and go to Data,
Data Validation, put in List and put in this as well
and include any empty fills in there.
So the moment something new comes in I will see it here.
But I'd rather not have all this empty ones here
and restrict my list to only the names that are unique,
so in this case restrict my list to three.
Because the size of my list is changing, I need to use
a formula that returns a range of different sizes.
One formula that I can use here is the offset function.
Now I have a separate video explaining offset
in a lot more detail, so in case this doesn't fully
make sense right here make sure you check that video out
and I'm going to put a link to it in the descriptions.
The first argument in the offset is your starting point,
I'm going to start right here 'cause that's close to my list,
and I could also start right here,
but I'm going to go with this one.
How many rows do I want to move down?
Well, I don't want to move any rows down,
I also don't want to move any columns, what I do want
is a flexible height here.
So the height of my range in this case should be three,
which formula could give me that?
Right, we know that COUNTA for example can count text,
so let's see if that would work here.
so I'm going to use COUNTA and put in all of these in here,
close the bracket, that's my height and my width is just one
and that's an optional argument by default it is one,
so I don't need to specify anything there.
Okay, so it's returning only one in the cell
but I can actually see it in here,
I'm going to click in the formula box and press F9.
And now I see all the empty ones as well.
So it looks like COUNTA counts these cells as well,
why does it do that?
Because I have a formula in there that returns nothing
and nothing is also counted, right?
So if I say COUNTA and I reference these here,
we get back 10, not three.
So I need another formula that gives back three,
and the other one I can think of is the COUNTIF function.
And I can take a look at this range, I'm going to use COUNTIF
in the proper sense and for the criteria I just want it
to be anything, some text that's in there.
So I can use a wild-card and I'm going to go
with the question mark because it means that
it's at least needs to have one character in there,
and then it can be as many characters long.
So I'm going to go with the star, put this in quotation marks
and close that, and now I get three, right?
So that's the formula that I can use in here,
so I just copied that, I'm going to replace the COUNTA part
with that formula.
Now let's just double check, I'm going to press F9
and I only get back my text, so Control + Z to go back.
Now I'm planning to copy paste this inside
my data validation, so what I'm going to do
is to fix everything, fix my cell references and press Enter
in this last bit actually I don't need because
that was my column optional argument, I can skip that.
Now I'm going to copy this, go here in Data Validation
and replace this with my offset formula, say okay
and now I only get what I see in here.
Okay, so let's remove this and now let's do
our final testing.
I'm going to add something here, I'm going to add New again here
okay, it only shows up once, does it show up here?
Yes, now let's go all the way to the bottom
and add in a Leila Div.
Let's go up here it, shows it here
and it shows it here as well.
So that's how you can use INDEX together with MATCH
together with COUNTIF and another INDEX to get
a unique list of items.
If you have other ways of doing this please share
your version in the comments below, I really appreciate it,
I'm sure the viewers of this channel
will also appreciate it.
As usual if you enjoyed this video don't forget to give
it a thumbs up, and if you haven't subscribed
to this channel, why not subscribe
so that you can get updates when new videos come out.