In column A, I get a list of name, which name can be duplicated, such as
John, Mary, John, Peter, Amy, Mary, Amy, Peter

I would like to create a menu for listed names, which name cannot be
duplicated, such as
Amy, John, Mary, Peter

Does anyone know how to do that in excel?
Thank you in advance
Eric

Hi Eric,
Try the Advanced Filter with a tick in the Unique records only option.
DataFilterAdvanced filter...

Ken Johnson

Thank you for your suggestion
Advanced filter is close, but is not exactly what I need.
Advanced filter can display a sorted list without duplication.
I have also tried the auto filter, which create a pull down menu to sorted
list and is closer, but it is still not exactly what I need, because the
first item can still be able to display, even through I select the second
one, and I don't know why.

I would like to create a pull down menu to display a sorted items, once the
item selected from the lists, then the rest of them will disappear.

Do you have any idea?
Thank you
Eric



"Ken Johnson" wrote:

Hi Eric,
Try the Advanced Filter with a tick in the Unique records only option.
DataFilterAdvanced filter...

Ken Johnson

Hi Eric,
That sounds like data validation where you first of all create your
sorted list of unique values using the Advanced filter then sort those
values. Then you select the range of cells that you want to have the
dropdown menu then go DataValidation then on the Settings tab sheet
pull down the "Allow:" menu and select "List" then make the "Source"
your list of sorted unique values.

Does this help?

Ken Johnson

"first of all create your sorted list of unique values using the Advanced
filter then sort those values."

I did that, for example
A,B,C,D,A,B,C,D then the sorted list becomes A,B,C,D,A, I don't know why the
A is duplicated in the sorted list, no matter what I finish this step.

"Then you select the range of cells that you want to have the
dropdown menu then go DataValidation then on the Settings tab sheet
pull down the "Allow:" menu and select "List" then make the "Source"
your list of sorted unique values."

I did that, the pull down menu create on the first item "A" in the list
A,B,C,D,A, but when I select any item under the pull down menu on the first
item, such as C, there is nothing change for the lists, which display
C,B,C,D,A

I would like to select C from the pull down menu, then the sorted lists will
display C,C, where two C are contained within A,B,C,D,A,B,C,D [Original list].

Do you have any idea?
Thank you
Eric


"Ken Johnson" wrote:

Hi Eric,
That sounds like data validation where you first of all create your
sorted list of unique values using the Advanced filter then sort those
values. Then you select the range of cells that you want to have the
dropdown menu then go DataValidation then on the Settings tab sheet
pull down the "Allow:" menu and select "List" then make the "Source"
your list of sorted unique values.

Does this help?

Ken Johnson

Hi Eric,
The duplicated item could be caused by an invisible character such as a
leading or trailing space on one and not the other.
Compare the two using the LEN function.
If leading or trailing space is the cause then you could process the
column using the TRIM function.

I think I'm still not following what it is that you are trying to do
with the dropdown menu.
What I was describing with the data validation was a way of setting up
a range of cells so that when one of those cells is selected a dropdown
arrow appears on the right side of that selected cell. The user can
then click on that arrow so that a list of all the values that are
allowed to be entered in that cell then appears. After the user
selects, from the dropdown list, the value to be entered and then
presses Enter, or Tab etc, that chosen value is entered into that
cell.Only one list value can be chosen.

If this is not what you are wanting to do then I'm am not understanding
what you are trying to do.

Ken Johnson