Remove duplicates from data validation list

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

You should be able to adapt this to your needs. With a list that contains duplicates in column M this formula (I put it in column P but you can use another column just change the reference to P$1:P1 to the column of your choice where the extraction will take place)
Enter in P2 and fill down until blank cells appear. This works equally well for numeric data. I used letters for ease of entry.

Formula:

Please Login or Register  to view this content.

Change the ranges to match your data.

If duplicate data in "A2:A13"
Firstly creat Table with range "A2:A13"
Go into "Name Manager" & define Name List & in Refer to "Offset" below formula.

PHP Code: 

=OFFSET(Sheet1!$A$2,0,0,COUNT(IF(Sheet1!$A$2:$A$1000="", "",1)),1) 


After that, In "B2"

PHP Code: 

=INDEX(List,MATCH(0,IF(MAX(NOT(COUNTIF($B$1:B1,List))*(COUNTIF(List,">"&List)+1))=(COUNTIF(List,">"&List)+1),0,1),0)) 


In "C2" creat "Data Validation List" Select from "Allow" as List & in source

PHP Code: 

=$B$2:$B$10 


Try something like this...

Data Range

	A	B	C	D	E
1	Data		Uniques		Dropdown List
2	1		1		1
3	1		2
4	2		3
5	3		4
6	3		5
7	3
8	4
9	4
10	4
11	5
12	------	------	------	------	------

This array formula** entered in C2:

=IFERROR(INDEX(A$2:A$11,MATCH(0,COUNTIF(C$1:C1,A$2:A$11),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, as the source for the drop down list use this formula:

=OFFSET(C2,,,SUMPRODUCT(--(C2:C100<>"")))

Seagull1. Please re-read rule 4. then please START YOUR OWN THREAD. To make it easier to get an answer:

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.