Listing Unique in Drop Downs values using Named Ranges

Hi Everyone,

As you can see in the picture, is the situation which I came across while serving at ExcelForum.com, So I decided to explain the work around. Actually, what I am doing is, Listing Unique Fruits for Smith and Jones in a Drop Down. I have used Named Ranges to make it more understandable.

Here are the steps:-

Making some Dynamic named ranges.

Note:- While Making these Named ranges, you have to select the any cell in the first row. This is Important as behaviour of the Named Ranges Depends up on that.

1. Name

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",100),Sheet1!$A:$A,1))

2. Fruit

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",100),Sheet1!$B:$B,1))

3. Converting Fruit Names (Text) to Numbers for reference purposes.

FruitNum

=MATCH(Fruit,Fruit,0)

Explanation:-

FruitNum further evaluates to {1;2;3;3;2;6;7}

Match function will match each fruit with in Fruit Array and return an array of row numbers. This array of Row numbers will represent the Fruit array as Parameter of Frequency functions because Frequency function don't take text as parameter.You can notice Fruit Kiwi is equivalent to 3 and so on others. (Different row number for different fruit and same row number for same fruit.)

4. RowArr

=IF(FREQUENCY(IF(Name=Sheet1!$D1,FruitNum),IF(Name=Sheet1!$D1,FruitNum,0))>0,ROW(Name))

This Named Range is important one. Here is the explanation.
Note:- I am explaining this in context of 2nd Row (cell $D2 to be precise). Sheet1!$D1 is a relative reference which changes to Sheet1!$D2 and so on when it goes downwards.

In the inner part, I have use IF function to filter the array to return only values corresponding to say Smith(Cell D2). like this
{FALSE;2;FALSE;FALSE;2;6;FALSE}
as first parameter of Frequency Function ie. Data Array. AND
{0;2;0;0;2;6;0}
as second parameter of Frequency function as Bin_Array.

There is a reason before using the IF function differently
That is, if you use 0 instead of False in Bin_Array, the resultant array (as returned by Frequency function) would be more or less becomes equivalent to our Index (Array or Table parameter )

I said more or less because Frequency function add one extra count element to resultant array to include 'more than maximum bin_array_value' condition.
I have used False in Data array because Frequency function just ignores any text or boolean value if there in data array.
Read this Article to understand it more.
Frequency function Explained ..!

So resultant array by Frequency Function (including one extra element) would be {0;2;0;0;0;1;0;0}

Now I have used an extra If Function to return the row no. of array values more than zero as they have fulfilled all criterias till now, the array would look like this:-
{FALSE;2;FALSE;FALSE;FALSE;6;FALSE;FALSE}

This RowArr Changes accordingly as Sheet1!$D1 (Smith) changes to Sheet1!$D2 (Jones) when Named Range is considered in context of next row.

Now its time for Sheet formula
=IFERROR(INDEX(Fruit,SMALL(RowArr,COLUMN(A$1))),"")

Put the above formula in G2 and Drag horizontally to return Unique Fruits relative to Smith and then Dragged downwards accordingly.

Dropdowns:-

Select Cell E2. Goto Data ----> Data Validation ----> List

Type =$G2:$H2 there press Ok.

I have, again, used relative reference that will switch to next row when Dragged vertically downwards.

Drag Cell E2 Down accordingly. The Drop down will automatically appear and Content of Drop down will change accordingly.

This was all from my side. Now its your turn to try it out.

Check the attached example file:-

Unique Dynamic Drop Downs.xlsx

Regards,
Vikas Gautam