Unique list for use with validation list

philobr

Active Member
Joined
Oct 17, 2005
Messages
280
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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Jared_8878

Board Regular
Joined
Apr 7, 2010
Messages
161
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?
 

Jared_8878

Board Regular
Joined
Apr 7, 2010
Messages
161
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

Active Member
Joined
Oct 17, 2005
Messages
280

ADVERTISEMENT

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

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
Here is an idea for a formula solution.

If you had this data list:
Excel Workbook
C
4Customer
5Joe
6Sioux
7Joe
8Moe
9Moe
10Chin
11YourName
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
4Unique Count
55
Sheet1
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Excel Workbook
NameRefers 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
5Return Unique Items
7Joe
8Sioux
9Moe
10Chin
11YourName
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
ABCDEFG
4DateSalesCustomerUnique CountSelect From Drop-down:
58/13/10$50Joe5Sioux
68/14/10$150SiouxReturn Unique Items
78/15/10$211JoeJoe
88/16/10$95MoeSioux
98/17/10$43MoeMoe
108/13/10$22ChinChin
118/13/10$1YourNameYourName
12
13
Sheet1


Then if you add a name, the data would look like this and the Data Validation List would increase:
Excel Workbook
ABCDEFG
4DateSalesCustomerUnique CountSelect From Drop-down:
58/13/10$50Joe6Sioux
68/14/10$150SiouxReturn Unique Items
78/15/10$211JoeJoe
88/16/10$95MoeSioux
98/17/10$43MoeMoe
108/13/10$22ChinChin
118/13/10$1YourNameYourName
128/14/10$85philobrphilobr
13
Sheet1


The data validation drop-down list would show this:

Joe
Sioux
Moe
Chin
YourName
philobr
 
Last edited:

philobr

Active Member
Joined
Oct 17, 2005
Messages
280

ADVERTISEMENT

Excellent, that worked, thanks.
 

Gonzo346

New Member
Joined
Aug 31, 2009
Messages
11
Just dropped by to see if this issue was similar to my problem.
Thanks for having shared such detailed response Mike!
Cheers.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top