Using a listbox to show every unique record in a range

Hi~

I am trying to insert a listbox by the way of validation and would like
to only have unique data displayed in it. I have seen some people
finding unique records but I don't know how they would display them. I
am trying to get them to display in a listbox with no luck. 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. I have
been trying to get it to work with the following code from:
http://groups.google.com/group/micro...b20e9fc5928a29

Code: in D7 =INDEX($B$8:$B$16,MATCH(0,--($B$8:$B$16=""),0))
Code: in D8
=INDEX(List1,MATCH(0,(List1="")+COUNTIF(D$7:D7,List1),0))

But I am having no luck, not to mention I don't know excel functions to
well.

Any help would be appreciated
Thank you for your time
Jeff

I just found this site where it says that the vba code will get you a
unique list. But it is not working. Do I have to do anything special
to this code? I cut and pasted it into vba editor and then called the
funcion on the spreadsheet. I am getting a #NAME? error. What is
causing this?

here is the site....
http://j-walk.com/ss/excel/tips/tip15.htm

I was planning on sending this list to the listbox

Jeff

Did you paste j-walk's code in a standard module?
How did you call it ?
Please post your code.

Cheers,
--
AP

<[email protected]> a écrit dans le message de news:
[email protected]...
>I just found this site where it says that the vba code will get you a
> unique list. But it is not working. Do I have to do anything special
> to this code? I cut and pasted it into vba editor and then called the
> funcion on the spreadsheet. I am getting a #NAME? error. What is
> causing this?
>
> here is the site....
> http://j-walk.com/ss/excel/tips/tip15.htm
>
> I was planning on sending this list to the listbox
>
> Jeff
>

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.

So I'm guessing nobody know's how to do this???