Populate column with values from another column - no repeats, and no empties

**badaboom55** · 08-23-2010, 03:18 PM

Hello All,

Column A has values which may or may not be unique (several cells in Column A may have the same value)
I want to populate Column B with these values, but I do not want to have repeated values, or empty spaces between the values, in Column B (see attached).

That's the meat of the issue, I can populate Column B with Column A values easy enough, but I'm having trouble getting rid of the repeated values and "collapsing" Column B so that it looks like in the attached xlsx.

So, that's the basic question I need answered. But, ultimately, I want Column C to allow a List of specific values (see attached). The allowed values will be defined by the values in Column A. However, I can't just point the validation straight to Column A, because then the in-cell dropdown in Column C would be extremely long and littered with duplicate values as well as empty spaces.

So, my first thought was to just populate Column B with a nice concise list of the values in Column A, and then use Column B as the Data Validation source. But, I'm open to a more elegant solution if someone has one; otherwise, just getting Column B to contain just the short list of values will be fine.

Thanks!

ps. I don't really care about the order of the values in Column B, just as long as it's one after the other.

**darkyam** · 08-23-2010, 04:32 PM

Copy column A, go to Advanced Filter, select Unique records only, and put it in column B. If you'd rather, you can opt to filter the list in place and not even have a column B.

**badaboom55** · 08-23-2010, 07:58 PM

Filtering doesn't accomplish what I need.

1) Applying the "unique records" filter to Column C does filter it, but it hides the ENTIRE ROW and not just the cells within Column C, so Column A needs to stay intact.

2) Also, filtering just changes what ROWS are displayed... but the values in the "hidden" rows are still accessible. So Column C would still have a bunch of duplicates in the drop-down.

3) I need Column B to update dynamically, so when a user inputs a new value into Column A, if it's unique, it'll get added to Column B, thus making it available as an option in Column C's drop-down. If a user adds a record and puts a value in Column A
that already exists, then Column B won't ultimately look any different, but the formula will still update the column.

I attached a new worksheet to better illustrate the problems, and what I want. I actually added a couple blank columns to show that Column A are the "records", Columns B + C (Now D and E) can be placed anywhere, I can put them in a new worksheet if need be, but the important thing is that Column A can be sorted in any fashion by the user and new entries can be added.

**darkyam** · 08-23-2010, 09:14 PM

There's a big difference between filtering and advanced filtering. Advanced filtering will take a list and either filter it in place, getting rid of all records that don't meet a certain criteria or are duplicates or take that list and paste it somewhere else, leaving the original list intact.

Wanting it to be dynamic adds a wrinkle to it, but if you have no problem hiding helper columns, perhaps something like the attached will work.