Excel Formula to Insert Distinct Values into a Column

I have a workshhet that can contain multiple rows with the same values in one of the columns. I want to create another worksheet, that will be hidden, that will be the source for a data validation list on a third sheet.

I prefer to do this with a formula instead of a macro because our company's security policies will prompt the user if the workbook contains macros. Can someone assit me with how this could possibly be accomplished.

As an example, sheet1 may contain the following values in column A:

A1
A1
A1
A2
A2
B1
B1
B1

I want to create a column in sheet2 that will contain:
A1
A2
B1
LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello dbbishop

highlight your list in Sheet2, then enter the range name

MyList

in the range name box above column A. Or use Insert - Name - Define - enter MyList as the name and in the refers to box click Sheet2 and highlight your data.

Now, in Sheet1 in the cells where you want the dropdown to appear, select the cells, click Data - Validation. Select Allow "List" and in the source field enter

=MyList

click OK.

You can copy the data validation to other cells this way: select the cell, click copy, select the target cell and use Paste Special - Validation - OK

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
See the screenshot with the data range that is called MyList and the data validation dialog where MyList is used for a drop down.

cheers, teylyn
DataValidation.gif
0
DaveCommented:
From you question title and example it looks like you need a list of unique values to use with the data validation list suggested by teylyn

One quick way is to use my Duplicate Master addin to produce a list of unique items from a column (or sheet, or even entire workbook) into a summary sheet. The addin can be downloaded at http://tiny.cc/6NjGV

Use the settings as below. Output summary also shown

Cheers

Dave

unique-list.jpg
unique-list-2.jpg
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Dave,

I read the question such that dbbishop wants to restrict the data entry in Sheet1 column A with data validation that has the list source on Sheet2, not that Sheet1 was providing a list with duplicates that needs to be consolidated into unique values for a data validation list. But re-reading the question, I feel you may have a point. :-)

dbbishop, can you please clarify?

cheers, teylyn
0
DaveCommented:
Your view is just as valid - probably more so actually :)
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The "third sheet" leans towards your approach though. Let's wait and see :-)
0
dbbishopAuthor Commented:
Okay, clarification. I know how to create the validation list. In fact I already have it, the trouble is it is duplicating entries (would be nice if the validation window allowed an ignore duplicate checkbox along with ignore blank.)

I initially tried using an advanced filter to copy the data to a different location to the far right on sheet1 and that appears to work. My main concern is that this workbook is updated monthly and the total number of rows and number of duplicate rows can change from month to month. Is the filter a one-time application to the data, or if the source data changes, will the filter remain in affect?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello dbbishop,

I'm not sure but I would think that Dave's tool is not dynamic, i.e. would need to be run each time the values in Sheet1 column A change.

Attached please find a different approach using formulas.

From a list with duplicates and blanks in column A, a list of unique values is created in column C, using an array formula. Column D produces an alphabetically sorted version of that list, also with an array formula. Finally, in column E you see the cleaned list without any error messages at the bottom.

I've created a dynamic range name in column E, which will grow and shrink with the values, so this range name (ValList) can be used in data validation. Strictly speaking, column E is not required, since the ValList range name can be created from a combination of columns D and C, but it just looks cleaner and does not cost much to create.

In your real life scenario you can place columns C, D and E on a different sheet. To make the formulas more readable, I created two dynamic range names for List1 and UniqueList, so make sure to adjust these name definitions when you move the columns to a different sheet.

Now, any time you enter a new value in column A, the dynamic ValList will update automatically.

Is that something you can work with?

cheers, teylyn
Q-25580031-unique-list-from-dupl.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
teylyn: I think this will work, with just a slight modification. The data is being retrieved from SQL Server and is already in presentation form. As such, it is properly 'sorted' in that all but the last two rows are sorted, and the last two rows are a summary of all data. As such, Ibasically need a "clean unique list' where the sort order is preserved. The data, as presented, would be something like:

A1  C1 ...
A1  C2 ...
R1  C1 ...
R1  C2 ...
Z1  C1...
Z1  C2...
TOT  C1 ...
TOT  C2 ...

Where A1, R1, Z1, TOT correspond to the values in column A of your example. Thus, the validation would present data in the preserved order of A1, R1, Z1 and TOT. I've played around with your example but seem to just be messing things up. Can you help (basically, I need a cleaned unique list)?

0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In that case, just forget about columns D an E in my attached example and redefine ValList as

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH("ZZZ",Sheet1!$C:$C,1))

That will be the sort order of your original list with the duplicates removed, covering all text values

cheers, teylyn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.