drop down list
 

Hi,

I really need help on this:
Name
Albert
Albert
Bill
Cindy
Bill
Albert

When i create a drop down list with reference to the above name, how to I
creat a list of the above names such that the duplicated one won't appear
again? i.e. i only want to see "albert, bill, cindy" in one list. for some
reasons i cannot do it with sorting.

thanks,
Boris

Hi!

What are you using as the source for the drop down?

If your source is a range of cells that contain duplicates
then that's what you'll get.

Use an Advanced Filter to copy the unique values to
another location then use that new list as the source.

Biff

-----Original Message-----
Hi,

I really need help on this:
Name
Albert
Albert
Bill
Cindy
Bill
Albert

When i create a drop down list with reference to the
above name, how to I
creat a list of the above names such that the duplicated
one won't appear
again? i.e. i only want to see "albert, bill, cindy" in
one list. for some
reasons i cannot do it with sorting.

thanks,
Boris
.

Hi Boris,

Here's one technique that I like to use, especially if the data values
may change over time. Assume that your data starts in column B, wth the
"Name" header in B1. Making sure that cell A1 evaluates to zero, enter
this formula in cell A2 and copy it down as far as your name data will
extend:

=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)

Note that it increments only when it encounters a new value. Now,
starting in, say, cell C1, enter this formula and copy it down as far
as your data will extend:

=VLOOKUP(ROW(),A:B,2,0)

Note that it lists the unique values, followed by a series of #NA
cells. This VLOOKUP formula, as written, must start in row 1 to work
properly.

Now create a dynamic range: Insert Name Define and enter a name
like "NameList" and enter the following formula in the Refers To: field
and click OK:

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,NA()),1)

Note that for this formula to work (as written), column C cannot
contain any other entries (other than the second formula from above).

Now you can reference your defined range NameList to get your
drop-down.

I hope you find this useful,

Gary

Gary

That is a great solution.

I guess the only issue is deciding how far to copy the formulas down when
you set the file up. Actually thinking about it, you could probably have a
Worksheet_Change event copy populate the formulas as more data gets added.

Regards
Rowan

"GaryDK" wrote:

Hi Boris,

Here's one technique that I like to use, especially if the data values
may change over time. Assume that your data starts in column B, wth the
"Name" header in B1. Making sure that cell A1 evaluates to zero, enter
this formula in cell A2 and copy it down as far as your name data will
extend:

=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)

Note that it increments only when it encounters a new value. Now,
starting in, say, cell C1, enter this formula and copy it down as far
as your data will extend:

=VLOOKUP(ROW(),A:B,2,0)

Note that it lists the unique values, followed by a series of #NA
cells. This VLOOKUP formula, as written, must start in row 1 to work
properly.

Now create a dynamic range: Insert Name Define and enter a name
like "NameList" and enter the following formula in the Refers To: field
and click OK:

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,NA()),1)

Note that for this formula to work (as written), column C cannot
contain any other entries (other than the second formula from above).

Now you can reference your defined range NameList to get your
drop-down.

I hope you find this useful,

Gary

Hi

Select the range with names + some amount of empty cells below, when you are
adding names to table in future. Create a pivot table, which counts all
names in selected range, without any totals (you can set this in table
options). Save the result table on separate sheet. You get something like
this:

Count of Name
Name Total
Albert 3
Bill 2
Cindy 1
(blank)

Select the first name in result table, and create a named range (in my
example, the first name was in cell A5=
NamesList=OFFSET(SheetWithPivot!$A$5,,,COUNTA(Shee tWithPivot!$B:$B)-1,1)

Now, into data validation list (I assume your dropdown is created usind data
validation list) source enter the formula
=NamesList

Whenever you enter new names into your table, you have to refresh the pivot
table.

Another approach after you get the list of existing names (without 'blank'),
is to copy them to separate sheet, p.e. Names, like this (header in A1):
Name
Albert
Bill
Cindy

and define the named range slightly differently
NamesList=OFFSET(Names!$A$2,,,COUNTA(Names!$A:$A)-1,1)

After that select all entries with names in your table + some amount of
empty cells below them, and implement the data validation list vith source
=NamesList
to them. Now you can enter only values which are in NamesList as names
(except copy-paste operation). When you want to enter some new name, you
have to enter on Names sheet at first.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets





"drop down list" <drop down wrote in message
...
Hi,

I really need help on this:
Name
Albert
Albert
Bill
Cindy
Bill
Albert

When i create a drop down list with reference to the above name, how to I
creat a list of the above names such that the duplicated one won't appear
again? i.e. i only want to see "albert, bill, cindy" in one list. for some
reasons i cannot do it with sorting.

thanks,
Boris

Thanks, Rowan. And thanks to Arvi, too.

I admire the pivot table technique posted by Arvi. Using a pivot table
in this manner is a clever idea that suggests that pivots may be
fertile ground for various solutions. Hmmm...

Gary