Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 wrote in message ps.com... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
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 wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting up a validation of data listbox to provide the unique items within a range
I just sent it.........Let me know if you didn't get it.
Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Data validation named range update | Excel Discussion (Misc queries) | |||
Finding unique items in data field for pivot tables | Excel Discussion (Misc queries) |