Display unique values in Listbox

[dave8]

I have a list on a worksheet:

NameLast NameFirst
Doe John
Smith Betty
Henry Bill
Chen Paul
Doe John
Henry Bill


I don't want to show the duplicate names in the list box. Is there a way to read only the unique names into a listbox?

 

[MisterBate5]

Are you asking about a data validation dropdown, or a listbox on a user form? If the first, simplest way I've found is to pivot the list and link the data validation to a named range on the pivot. If the second, you can use a scripting.dictionary object to get your unique list (adding if it doesn't exist, do nothing if it does, then read back the array of keys).

 

[dave8]

The scripting dictionary you mentioned is very new to me and I don't understand how to use it. Can you explain or point me to a source? I appreciate your help.

 

[MisterBate5]

Google "scripting.dictionary" or take a look at: http://msdn.microsoft.com/en-us/library/x4k5wbx4(v=vs.85).aspx

To use it, either add a reference to the MS Scripting Runtime or use:

dim x as object
set x = createobject("scripting.dictionary")

and then use code similar to the following (UNTESTED):

dim names as variant
dim n as long
' process names, adding each new one seen into dictionary
for each aCell in range("A2:A7")
  name = acell.text & " " & acell.offset(0,1).text
  if x.exists(name) then x.add(name, name)
next acell
 
' do something with the names
names = x.keys ' populates with array of strings
for n = lbound(names) to ubound(names)
  ' do something with each names(n)
next n