Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,Lis t1),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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a listbox to show every unique record in a range
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a listbox to show every unique record in a range
Did you paste j-walk's code in a standard module?
How did you call it ? Please post your code. Cheers, -- AP a écrit dans le message de news: ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a listbox to show every unique record in a range
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a listbox to show every unique record in a range
So I'm guessing nobody know's how to do this???
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting data points and show a target range on the same chart. | Charts and Charting in Excel | |||
Unique identifier | Excel Discussion (Misc queries) | |||
Display unique record | Excel Discussion (Misc queries) | |||
Program Column B to record numerical range based on number in colm | Excel Discussion (Misc queries) | |||
Why does my advance filter only show one record? | Excel Discussion (Misc queries) |