Data validation - list

[Tiina]

Hi,
I'm setting up a Data Validation cell using 'List". However, my source data has each option on the list more than once; therefore my drop-down list contains each of the options more than once. Is there a way to make this drop-down list have each option available just once. I know I can create a separate 'spot' for my list options and 'remove duplicates... but I'd like to avoid that if I can.
Thank you!

 

[Eric W]

This kind of piqued my interest. How to create a sorted list of unique items. For what it's worth, I came up with this:

	H	I
cat	ape
dog	cat
ape	dog
mouse	gerbil
cat	horse
horse	mouse
mouse
gerbil

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I1[/TH]
[TD="align: left"]{=INDEX(H:H,MAX(IF(COUNTIF($H$1:$H$8,"<"&$H$1:$H$8)=0,ROW($H$1:$H$8))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]{=IF(I1="","",IF(SUM(IF(COUNTIF($I$1:$I1,$H$1:$H$8),0,1))=0,"",INDEX(H:H,MATCH(MIN(COUNTIF($H$1:$H$8,"<"&IF(COUNTIF($I$1:$I1,$H$1:$H$8)=0,$H$1:$H$8,"zzz"))),COUNTIF($H$1:$H$8,"<"&IF(COUNTIF($I$1:$I1,$H$1:$H$8)=0,$H$1:$H$8,"zzz")),0))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in I1 and confirm with Control+Shift+Enter. Put the formula in I2 and confirm with Control+Shift+Enter. Then copy I2 down the column as far as necessary.

It does work, however, I can't speak to its efficiency. It doesn't handle empty rows well, either, although I'm working on that. I suspect that you'd do better with either a pivot table or an on-demand macro that you'd call whenever your list changes.

 

[Tiina]

THANK YOU! I will play with it and let you know how it works. I have more data to test it out with and the data changes daily so it's even more fun. Much much appreciated!
My pivot table works well, I think. It never occurred to me that refreshing a pivot can become the basis of a list.

I was very excited when 'remove duplicates' function became available in Excel few years back. Now I'm going to root for a 'dynamic' remove duplicates function that gives us a of unique values via a formula!

 

[Eric W]

For what it's worth, here's a version that allows empty cells:

	H	I	J	K
cat	aardvark
dog	ape
ape	cat
mouse	dog
cat	gerbil
horse	horse
mouse	mouse
gerbil	snake
zebra
zebra
aardvark
snake
SNAKE
Snake
cat

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J1[/TH]
[TD="align: left"]{=INDEX(H:H,MAX(IF((COUNTIF($H$1:$H$20,"<"&$H$1:$H$20)=0)*($H$1:$H$20<>""),ROW($H$1:$H$20))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]{=IF(ROW()>$K$1,"",INDEX(H:H,MATCH(MIN(COUNTIF($H$1:$H$20,"<"&IF((COUNTIF($J$1:$J1,$H$1:$H$20)=0)*($H$1:$H$20<>""),$H$1:$H$20,"zzz"))),COUNTIF($H$1:$H$20,"<"&IF(COUNTIF($J$1:$J1,$H$1:$H$20)=0,$H$1:$H$20,"zzz")),0)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K1[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IFERROR(MATCH(H1:H20,H1:H20,0),FALSE),ROW(H1:H20)),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put in the J1 and K1 formulas. Then put in the J2 formula and copy down. Actual empty cells are ignored. Cells containing spaces will be considered unique entries. Cells containing nulls as a result of a formula will throw things off.

And to test the efficiency, I created a list of 3000 items and put the formulas in. It took 45 minutes to calculate the sheet. Based on that, these formulas should only be used on short lists. Your 3000 item list can be handled much better with the pivot table or a macro.

 

[Tiina]

Thanks! I'm going to try this anyway. Just to see how it works and confirm that it would take a long long time in our environment here as well. Thank you again for your reply!