Create dropdown list in Excel that will only list unique entries

[MissAudrey]

Here is a simplified example of what I am trying to do:

Create dropdown list in Column C that will list fruit associated with last name but will remove duplicates. I have attached an excel example.

A B C
Name Fruit Dropdown
Smith Apples If A3=Smith, create dropdown that will list Apples, Oranges
Jones Kiwi If A4=Jones, create dropdown that will list Kiwi, Bananas
Jones Kiwi If A5=Jones, create dropdown that will list Kiwi, Bananas
Smith Apples If A6=Smith, create dropdown that will list Apples, Oranges
Smith Oranges If A7=Smith, create dropdown that will list Apples, Oranges
Jones Bananas If A8=Jones, create dropdown that will list Kiwi, Bananas

[Glenn Kennedy]

What effect does the entry in Col B have on anything?

[MissAudrey]

Columns A and B are just data entry fields.

I created a simplified example. In real-life, the drop down that I am trying to create will be on a different tab summarizing what was entered in columns A and B.

Does that help?

[Glenn Kennedy]

Does this look like the sort of thing you're after. Select items from the purple dropdown boxes... It all hangs together with a series of Named Ranges (CTRL + F3 to view...).

[MissAudrey]

Your example doesn't list any duplicate entries in the vehicles, landmarks,oceangoing.

My master list has multiple entries (e.g. vehicles: bus, bus, bus, car, lorry, lorry, articulated, articulated).

I want the drop down list to not list the redundant enteries.

[Glenn Kennedy]

Sily question... Why does your Master List have repeats? Why not use a formula to return a unique list? can you post a real example?

OK, that's 3 questions...

[sktneer]

If you are open to a VBA solution, please find the attached sheet. Sheet1 Module contains a code for Selection_Change Event and as per the code, once you select a cell in col. C, it will have the updated unique drop down list as per your requirement. So if you add more entries in col. A and B, the drop down list will be automatically updated with the new entries.
To view the code --> Right Click the Sheet1 Tab
Don't forget to Enable Marco while prompted when opening the workbook first time.

See if this is something you can work with.

[MissAudrey]

The VBA solution worked perfectly. The only problem is that I have no experience with VBA and I can't understand the code in order to adapt it to my spreadsheet.

I can't provide the actual spreadsheet because it contains proprietary information. The reason why it has duplicate entries is because there is another column that makes the entry combination unique.

I modified the spreadsheet with another example of what I am trying to achieve. See attached (Excel Unique dropdown list2.xlsx).

There are two tabs in the new spreadsheet example:
Tab 1 - Drop Down - The orange highlighted cells are the ones where I am trying to create the filtered drop-down.
Tab 2 - Shopping - This is where the data is originally entered.

I am up for creating a formula to create a unique list but I do not know how to do this. I would like the ability to be able to add entries to the "shopping" tab and have the drop-down list automatically updated with the new data.

Thanks for your help!

[sktneer]

Please find the attached sheet with the modified code.
This time a code for Sheet_Activate Event has been used for DropDown Sheet instead of Sheet_Selection Event. i.e. once you activate the DropDown sheet, the drop-downs will be automatically updated based on Shopping Sheet.
Also the code will place the Unique Names from col. A of Shopping Sheet on Col. A of DropDown Sheet. So now you don't need to input the unique names manually on DropDown Sheet.
If you don't want the message box to appear, please delete the Msgbox line of code which is the second last line of code just before End Sub.

Is this what you were trying to achieve?

[Vikas_Gautam]

I have used Named ranges to get the job done
Here are the steps:-
Named Ranges
Name

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

Fruit

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

FruitNum

=MATCH(Fruit,Fruit,0)

RowArr

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

Green Region (in cell J1)

=IFERROR(INDEX(Fruit,SMALL(RowArr,COLUMN(A$1))),"")

Then Drop down Starting from cell C2 with (in Data Validation List)

=$J2:$O2

Drag down

Check the attached file:-

[Vikas_Gautam]

I have done this(Post 10) for this for the first time. So may have chances of improvement. But I have made it efficient enough given my knowledge.
Here is the link having explanation of Frequency construction (Post 39) I have used.
http://www.excelforum.com/excel-form...ml#post3958135

[FlameRetired]

Can't tell if this is solved or not.

Using the first uploaded file I did something similar to Vika's approach.

There is a modified drop-down for Smith/Jones. The drop-down in column C works off a dynamic data validation formula (in Name Manager).

I couldn't tell what you wanted in column B so it just repeats the contents of column C.

If this is on the right track I'll attempt something with your second upload.