fetch

Excel: Extract unique items for dynamic data validation drop down list



Sharing buttons:

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.