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 |
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) |