Setting up a validation of data listbox to provide the unique items within a range

[jedale]

I am trying to insert a listbox by the way of data validation and would
like
to only have unique data displayed in it. I was wondering if anybody
has done this before or if it is possible. I would like it to remove
any and all records that are blank.

This is what I have got so far.................

VBA Code:
Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number
' of unique elements
' If Count = False, the function returns a variant array of unique
' elements

Next i

AddItem:
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If

Next Element

' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

Then I input an array with a few duplicate Item and us the function to
determine the list. So far I found that it worked for the following
function:
{=TRANSPOSE(UniqueItems(A4:A27))}
but this only gives me the number of unique items in the array. The
problem is when I try to use the following function:
{=TRANSPOSE(UniqueItems(A3:A26,FALSE))}
This now only returns a zero and if I fill down they all are zero.

I would like to get a list of unique items from this formula. Example
list would be:
{Array = Lorem, Lorem, foo, bar, bar} {Formula_returns = Lorem, foo,
bar}
I haven't a clue how to display this in a regular excel cell box so I
thought that using a validation list box would inherently work.

Thank you for your time!!
Jeff

 

[Biff]

Hi!

Want a worksheet formula to extract the uniques?

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=SUMPRODUCT((A$4:A$27<>"")/COUNTIF(A$4:A$27,A$4:A$27&"")),INDEX(A$4:A$27,SMALL(IF(A$4:A$27<>"",IF(MATCH(A$4:A$27,A$4:A$27,0)=ROW(A$4:A$27)-ROW(A$4)+1,ROW(A$4:A$27)-ROW(A$4)+1)),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

 

[jedale]

Thanks Bill, that is just what I needed!! Thank you.

I was also wondering if there was some way of putting these values into
a data validation select box? Can this be done? Has it been done
before? Any ideas on how I would go about making this happen would be
helpful!

Thanks for your help.
Jeff

 

[Biff]

Are you wanting this to happen using VBA code or through normal worksheet
actions?

If you want this through VBA code I can't help with that.

Biff

 

[jedale]

I am also not familiar with VBA. Thanks to your code I now have my
excel sheet working. It is not perfect but it will have to do. I had
to do an itermediate step to get the unique data into a validation
select box. First I used your code to get me all the uniques from an
array and then I put the unique list into a validation select box.
This still has it's problems. I made the array longer than was needed
before I gave it to the select box in case there was any additional
records added in the future. This takes care of what I wanted to make
the worksheet do but it has alot of blanks and 'FALSE' items solely
there for expansion. Do you know how to change the function to return
as a blank field if there are no more unique items available instead of
returning 'FALSE'.

Thank you for your time.

Jeff

 

[Biff]

Where do the FALSE items come from?

You can use dynamic ranges in both your original data set (where you're
extracting the uniques from) and then as the source (the extracted uniques
list) for the drop down list.

If you're interested I can put together a sample file that demonstrates
this.

Biff

 

[jedale]

That would be really helpful if it's not to much to ask.

The FALSE attributes are coming from the function returning no more
unique entries. I could just limit the function by filling down to the
last unique entry, but this wouldn't allow for any future expansion on
my lists. Consequently, I would have to manually go through the and
check the lists from time to time to make sure the function is
returning all known unique items by filling down cells. I was just
wondering if there was a way to hide the FALSE attribute from showing
up in the list once all unique entries are given so that they wouldn't
be in the validation of data select box.

Thanks again,
Jeff

 

[Biff]

The FALSE attributes are coming from the function returning no more

unique entries.

That shouldn't happen. The formula to extract the uniques should only return
either a unique item or leave the cell blank. You may not have implemented
it correctly.

It would be easier if you were to send me a copy of your file then I could
see exactly what you're wanting to do and in what context. If you can do
that my email address is:

xl can help at comcast period net

Remove "can" and change the obvious.

If you can't send your file at least send me an email so I can return to you
the sample file.

Biff

 

[jedale]

I just sent it.........Let me know if you didn't get it.

Jeff