Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic list to remove duplicates and spaces ?
Hi,
I have a drop-down box I'd like to fill with unique, alphabetized names sourced from another sheet. However, the list of names on the other sheet contains duplicate name entries and varies in length as the list is updated. Currently I reference the entire column that contains the names, which results in duplicate entries, and many many spaces. Is there a way to dynamically reference this list so that the drop-down doesn't have any duplicates or spaces? Preferably I'd like to do this in the ListFillRange property of the combobox, and without any VB code. Thanks, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic list to remove duplicates and spaces ?
To do what you want, you would need to use complex formulas to build you
list of unique, alphabatized names and then probably a defined name to pick up that new list. I assume you can figure out how to do that. -- Regards, Tom Ogilvy "Keith" wrote in message om... Hi, I have a drop-down box I'd like to fill with unique, alphabetized names sourced from another sheet. However, the list of names on the other sheet contains duplicate name entries and varies in length as the list is updated. Currently I reference the entire column that contains the names, which results in duplicate entries, and many many spaces. Is there a way to dynamically reference this list so that the drop-down doesn't have any duplicates or spaces? Preferably I'd like to do this in the ListFillRange property of the combobox, and without any VB code. Thanks, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic list to remove duplicates and spaces ?
Hi Keith;
Take the column for the sort list and select one of the cells. The use the code ActiveCell.EntireColumn.Select Then filter the list to some where else maybe even a different sheet with the unique values using this code. Range(Selection.Address).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1" _ ), Unique:=True You can change the CopyToRange to be the sheet and range you want Then set the combo box to read the CopyToRange Thanks, Greg -----Original Message----- Hi, I have a drop-down box I'd like to fill with unique, alphabetized names sourced from another sheet. However, the list of names on the other sheet contains duplicate name entries and varies in length as the list is updated. Currently I reference the entire column that contains the names, which results in duplicate entries, and many many spaces. Is there a way to dynamically reference this list so that the drop-down doesn't have any duplicates or spaces? Preferably I'd like to do this in the ListFillRange property of the combobox, and without any VB code. Thanks, Keith . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic list to remove duplicates and spaces ?
Greg and Tom,
These suggestions look good, though Tom, your assumption was wrong. I'm not so adept at excel formulas. Greg, your recommendation looks good. Two questions: 1) I will add more data to the list, so its length will increase. This means setting the list to equal the CopyToRange won't work since it needs to be dynamically sized. Any recommendations? 2) Is there any way I can create this list only using excel formulas? What if all I need to do is set the Name list range dynamically. By this I mean I have a column of data that will grow with time and I want to set the Name list range to be A1:A(last cell with data). Thanks again and please let me know if anything needs clarification. Keith "Gjones" wrote in message ... Hi Keith; Take the column for the sort list and select one of the cells. The use the code ActiveCell.EntireColumn.Select Then filter the list to some where else maybe even a different sheet with the unique values using this code. Range(Selection.Address).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G1" _ ), Unique:=True You can change the CopyToRange to be the sheet and range you want Then set the combo box to read the CopyToRange Thanks, Greg -----Original Message----- Hi, I have a drop-down box I'd like to fill with unique, alphabetized names sourced from another sheet. However, the list of names on the other sheet contains duplicate name entries and varies in length as the list is updated. Currently I reference the entire column that contains the names, which results in duplicate entries, and many many spaces. Is there a way to dynamically reference this list so that the drop-down doesn't have any duplicates or spaces? Preferably I'd like to do this in the ListFillRange property of the combobox, and without any VB code. Thanks, Keith . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic list to remove duplicates and spaces ?
Hi Keith,
See Debra Dalgleish's tutorial on dynamic ranges: http://www.contextures.com/xlNames01.html#Dynamic --- Regards, Norman "Keith" wrote in message om... <Snip 1) I will add more data to the list, so its length will increase. This means setting the list to equal the CopyToRange won't work since it needs to be dynamically sized. Any recommendations? 2) Is there any way I can create this list only using excel formulas? What if all I need to do is set the Name list range dynamically. By this I mean I have a column of data that will grow with time and I want to set the Name list range to be A1:A(last cell with data). <Snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove duplicates found in master list | Excel Discussion (Misc queries) | |||
Remove Duplicates; How can I get a list of items removed? | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
How to remove duplicates from a list and copy new list to new colu | Excel Worksheet Functions | |||
Remove spaces | Excel Programming |