?
Solved

Excel Formula to Insert Distinct Values into a Column

Posted on 2010-03-28
10
Medium Priority
?
425 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:dbbishop
  • 6
  • 2
  • 2
10 Comments
 
LVL 50
ID: 28933315
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
 
LVL 50
ID: 28933563
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 28940251
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
LVL 50
ID: 28941605
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 28942624
Your view is just as valid - probably more so actually :)
0
 
LVL 50
ID: 28942754
The "third sheet" leans towards your approach though. Let's wait and see :-)
0
 
LVL 15

Author Comment

by:dbbishop
ID: 28982139
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 29018363
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
 
LVL 15

Author Comment

by:dbbishop
ID: 29161588
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
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 29165302
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question