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,SMAL L(IF(A$4:A$27<"",IF(MATCH(A$4:A$27,A$4:A$27,0)=RO W(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

wrote in message
ups.com...
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