How to create menu for listed names?
 

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

How to create menu for listed names?
 

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

Ken Johnson

How to create menu for listed names?
 

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

How to create menu for listed names?
 

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

How to create menu for listed names?
 

"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

How to create menu for listed names?
 

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

How to create menu for listed names?
 

In column A, I get a list of name, age and task, which name can be
duplicated, such as

Name Age Task
John 13 Cleaning room
Mary 20 Pick up Book
John 13 Buy candy
Peter 32 Borrow book from library
Amy 22 Return book to library
Mary 20 Wash disks
Amy 22 Watch TV
Peter 32 Go shopping

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

Pull down menu include following items
Amy
John
Mary
Peter

If I select Amy, then it only displays following infomation

Amy 22 Return book to library
Amy 22 Watch TV

It just like manage a database with filter, but I do it in excel
Do you have any idea?
Thank you

Eric

How to create menu for listed names?
 

Hi Eric,
I think, and hope, I now understand what you are trying to do!
You want to be able to click on an autofilter down arrow and select a
name then have the autofilter hide all the rows that don't start with
that name in column A, right?

I think you will need to set up a column that only has the names. In
any column that is spare and visible on the screen without having to
scroll over to it, enter the following formula into its row 2 cell...

=LEFT(A2,FIND(" ",A2,1)-1)

That's a space between the ""s.
Then filldown that formula to the bottom of your data.
After you have done that you should see in that column only the names,
not their age and not their task.
Now all you have to do is select that column and apply the autofilter,
which will only show unique names, however, when you choose a name only
rows with that name are visible.

I hope this is useful.

Ken Johnson

How to create menu for listed names?
 

Hi Eric,
I think, and hope, I now understand what you are trying to do!
You want to be able to click on an autofilter down arrow and select a
name then have the autofilter hide all the rows that don't start with
that name in column A, right?

I think you will need to set up a column that only has the names. In
any column that is spare and visible on the screen without having to
scroll over to it, enter the following formula into its row 2 cell...

=LEFT(A2,FIND(" ",A2,1)-1)

That's a space between the ""s.
Then filldown that formula to the bottom of your data.
After you have done that you should see in that column only the names,
not their age and not their task.
Now all you have to do is select that column and apply the autofilter,
which will only show unique names, however, when you choose a name only
rows with that name are visible.

I hope this is useful.

Ken Johnson