Unique list for use with validation list

[philobr]

How can I get a validation list to display a unique list of items?

My list contains multiple instances of the same entries Im trying to get a validation list to display the unique values so:

Item1
Item1
Item2
Item2
Item2
Item3
Item3

will display in the validation list as

Item1
Item2
Item3

I can then use this to populate a second validation list.

Any ideas?

 

[Jared_8878]

Is the list static - ie you can just do a filter on it?

Is is it dynamic and will be changing such that you don't want to have to manually filter?

 

[Smitty]

See: http://www.ozgrid.com/forum/showthread.php?t=28989

 

[Jared_8878]

If dynamic, what I have done in the past:


select the column where the list is
insert pivot
drop that column into the row labels

then select that data plus a few more rows incase the list expands and give that range a name

then go to your desired cell and do data validation with that range name.

don't forget to right click on the pivot > pivot table options > data > refresh data when opening the file

 

[philobr]

Thanks for the replies, the list will change.

I need to be able to select the first drop down, then the second drop down
from A & B, then have C and D auto populated.

I think I have it fixed, I got the dependent drop down for A & B,
then used a VLOOKUP to get what I needed.

 

[mgirvin]

Here is an idea for a formula solution.

If you had this data list:

Excel Workbook
	C
4	Customer
5	Joe
6	Sioux
7	Joe
8	Moe
9	Moe
10	Chin
11	YourName
Sheet1

And you wanted the data validation drop-down list to show this:

Joe
Sioux
Moe
Chin
YourName


And you wanted the list and the data validation drop-down to be dynamic (can add or subtract values) try this:

1) Create Defined Name (Ctrl + F3 to get to Name Manager):

"Customers":

=Sheet1!$C$5:INDEX(Sheet1!$C$5:$C$19,COUNTA(Sheet1!$C$5:$C$19))

The range Sheet1!$C$5:$C$19 should go down beyond the final row that you would have data.



2) Create this formula:

Excel Workbook
	E
4	Unique Count
5	5
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
Sheet1!Customers	=Sheet1!$C$5:INDEX(Sheet1!$C$5:$C$19,COUNTA(Sheet1!$C$5:$C$19))
Worksheet Defined Names

3) Create Unique List (green cells are formulas):

Excel Workbook
	E
5	Return Unique Items
7	Joe
8	Sioux
9	Moe
10	Chin
11	YourName
12	.
13	.
Sheet1

In cell E7 enter this formula with Ctrl + Shift + Enter, and then copy down as far as needed to accommodate unique values:

=IF(ROWS(E$7:E7)>$E$5,"",INDEX(Customers,SMALL(IF(FREQUENCY(IF(Customers<>"",MATCH(Customers,Customers&"",0)),ROW(Customers)-ROW($C$5)+1),ROW(Customers)-ROW($C$5)+1),ROWS(E$7:E7))))

4) Create Defined Name (Ctrl + F3 to get to Name Manager):

"DVDynamicRange":

=Sheet1!$E$7:INDEX(Sheet1!$E$7:$E$13,Sheet1!$E$5)

The range Sheet1!$E$7:$E$13 should go down beyond the final row that you would have unique data.



5) Create Data Validation Drop-down List (Alt + D + L, Allow = List, Source = Defined Name = DVDynamicRange)

The whole thing might look like this:

Excel Workbook
	A	B	C	D	E	F	G
4	Date	Sales	Customer		Unique Count		Select From Drop-down:
5	8/13/10	$50	Joe		5		Sioux
6	8/14/10	$150	Sioux		Return Unique Items
7	8/15/10	$211	Joe		Joe
8	8/16/10	$95	Moe		Sioux
9	8/17/10	$43	Moe		Moe
10	8/13/10	$22	Chin		Chin
11	8/13/10	$1	YourName		YourName
12
13
Sheet1

Then if you add a name, the data would look like this and the Data Validation List would increase:

Excel Workbook
	A	B	C	D	E	F	G
4	Date	Sales	Customer		Unique Count		Select From Drop-down:
5	8/13/10	$50	Joe		6		Sioux
6	8/14/10	$150	Sioux		Return Unique Items
7	8/15/10	$211	Joe		Joe
8	8/16/10	$95	Moe		Sioux
9	8/17/10	$43	Moe		Moe
10	8/13/10	$22	Chin		Chin
11	8/13/10	$1	YourName		YourName
12	8/14/10	$85	philobr		philobr
13
Sheet1

The data validation drop-down list would show this:

Joe
Sioux
Moe
Chin
YourName
philobr

 

[philobr]

Excellent, that worked, thanks.

 

[mgirvin]

EXCELlent! I am glad that it worked.

 

[Gonzo346]

Just dropped by to see if this issue was similar to my problem.
Thanks for having shared such detailed response Mike!
Cheers.

 

[mgirvin]

When I can get time away from my job (sometimes 80+ hours a week), my favorite things to do is hang out at the Mr Excel Message Board! I am glad that post helped!