Today's topic is to extract unique values from a list of data, and we're
going to do this with formulas. (upbeat music) 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. (upbeat music) 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.
