Unique list for use with validation list

P

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?
 
Sort by date Sort by votes
mgirvin said:
EXCELlent! I am glad that it worked.
Click to expand...

I know this is an old post, but I have a question. (My 1st post by the way!)

I followed everything as it was listed and was successful. But when I tried to do the exact same thing for a different column of data I end up with #N/A in the "Routine Unique Items" cell. Not sure why. The only thing I can think of is I am looking up numerical values, whereas the one I got to work was a text field. Does the array work for both text and number fields?
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Aladin Akyurek said:
Care to post a small sample?
Click to expand...

I basically copied what was in the original post, but substituted "Customers" for "ItemList" in the array below.

{=IF(ROWS(J$24:J24)>$J$22,"",INDEX(ItemList,SMALL(IF(FREQUENCY(IF(ItemList<>"",MATCH(ItemList,ItemList&"",0)),ROW(ItemList)-ROW($D$2)+1),ROW(ItemList)-ROW($D$2)+1),ROWS(J$24:J24))))}

I keep getting the #N/A error

But when I use the following array for "FactoryList" it works fine (which is what I first started using and thought I hit a home run with this find).

{=IF(ROWS(J$7:J7)>$J$5,"",INDEX(FactoryList,SMALL(IF(FREQUENCY(IF(FactoryList<>"",MATCH(FactoryList,FactoryList&"",0)),ROW(FactoryList)-ROW($C$2)+1),ROW(FactoryList)-ROW($C$2)+1),ROWS(J$7:J7))))}

Again the only difference I see is the factory list is a text field, and the Item List is numerical. Didn't know if there was a function listed within the array that is explicit to text only. Driving me nuts! Hope you can help.
 
Upvote 0
You have a list which is names as Customers and you want to create a unique list from Customers in column J from J24 on. Is this the right description?
 
Upvote 0
smittyssluggers said:
Yes, except the original post used the array as "Customer", I am using it as "ItemList" and it starts at J24 and following.
Click to expand...

Define Ivec using Formulas | Name Manager as referring to:
Rich (BB code): 
=ROW(ItemList)-ROW(INDEX(ItemList,1,1))+1

J24, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(ItemList,SMALL(IF(FREQUENCY(IF(ItemList<>"",
  MATCH(ItemList,ItemList,0)),Ivec),Ivec),ROWS($J$24:J24))),"")
 
Upvote 0
Let's assume the data in A5:C12 is actually auto-filtered so column A (for example) is only showing dates between 8/14/10 to 8/16/10. Is there a solution where the Unique Count will be lessened, the Return Unique Items will only show the filtered customer names, and most importantly the drop down only contains the those filtered customer names.
 
Upvote 0
Zorod said:
Let's assume the data in A5:C12 is actually auto-filtered so column A (for example) is only showing dates between 8/14/10 to 8/16/10. Is there a solution where the Unique Count will be lessened, the Return Unique Items will only show the filtered customer names, and most importantly the drop down only contains the those filtered customer names.
Click to expand...

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
4​
[/td][td]Date[/td][td]Sales[/td][td]Customer[/td][/tr]

[tr][td]
6​
[/td][td]
8/14/2010​
[/td][td]
$150 ​
[/td][td]Sioux[/td][/tr]

[tr][td]
7​
[/td][td]
8/15/2010​
[/td][td]
$211 ​
[/td][td]Joe[/td][/tr]

[tr][td]
8​
[/td][td]
8/16/2010​
[/td][td]
$95 ​
[/td][td]Moe[/td][/tr]

[tr][td]
12​
[/td][td]
8/14/2010​
[/td][td]
$85 ​
[/td][td]philobr[/td][/tr]

[tr][td]
13​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td]
4​
[/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td]#List#[/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]Joe[/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]Sioux[/td][td][/td][td][/td][/tr]

[tr][td]
19​
[/td][td]Moe[/td][td][/td][td][/td][/tr]

[tr][td]
20​
[/td][td]philobr[/td][td][/td][td][/td][/tr]

[tr][td]
21​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

A5:A12 is named as Date.
B5:B12 is named as Sales.
C5:C12 is named as Customer.

Ivec is defined as referring to:
Rich (BB code): 


=ROW(Customer)-ROW(INDEX(Customer,1,1))+1

A15, control+shift+enter (cse), not just enter:
Rich (BB code): 


=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,
   OFFSET(Customer,ROW(Customer)-ROW(INDEX(Customer,1,1)),,1)),
   MATCH(Customer,Customer,0)),Ivec),1))

A17, cse and copy down:
Rich (BB code): 


=IFERROR(INDEX(Customer,SMALL(IF(FREQUENCY(IF(Customer<>"",
   IF(SUBTOTAL(3,OFFSET(Customer,
     ROW(Customer)-ROW(INDEX(Customer,1,1)),,1)),
   MATCH(Customer,Customer,0))),Ivec),Ivec),ROWS(A$17:A17))),"")

Define DVList as:
Rich (BB code): 


=OFFSET(Sheet1!$A$17,0,0,Sheet1!$A$15)
which you can use as Source in data validation if you will.
 
Last edited:
Upvote 0
mgirvin said:
Here is an idea for a formula solution.

If you had this data list:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]C[/TH]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: #002060"]Customer[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Joe[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Sioux[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Joe[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Moe[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]Moe[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]Chin[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]YourName[/TD]
[/TR]
</tbody>[/TABLE]




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:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]E[/TH]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: #002060"]Unique Count[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="bgcolor: #CCFFCC, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


CellFormula
E5=SUM(IF(FREQUENCY(IF(Customers<>"",MATCH("~"&Customers,Customers&"",0)),ROW(Customers)-ROW(C5)+1),1))

<tbody>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Defined Names [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]


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

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]E[/TH]
[/TR]
[TR]
[TH]5[/TH]
[TD="bgcolor: #002060"]Return Unique Items[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="bgcolor: #CCFFCC"]Joe[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="bgcolor: #CCFFCC"]Sioux[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="bgcolor: #CCFFCC"]Moe[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="bgcolor: #CCFFCC"]Chin[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="bgcolor: #CCFFCC"]YourName[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="bgcolor: #CCFFCC"].[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="bgcolor: #CCFFCC"].[/TD]
[/TR]
</tbody>[/TABLE]




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:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: #002060"]Date[/TD]
[TD="bgcolor: #002060"]Sales[/TD]
[TD="bgcolor: #002060"]Customer[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Unique Count[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Select From Drop-down:[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$50[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC, align: right"]5[/TD]
[TD][/TD]
[TD]Sioux[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]8/14/10[/TD]
[TD="align: right"]$150[/TD]
[TD]Sioux[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Return Unique Items[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]8/15/10[/TD]
[TD="align: right"]$211[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]8/16/10[/TD]
[TD="align: right"]$95[/TD]
[TD]Moe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Sioux[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]8/17/10[/TD]
[TD="align: right"]$43[/TD]
[TD]Moe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Moe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$22[/TD]
[TD]Chin[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Chin[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$1[/TD]
[TD]YourName[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]YourName[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




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

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
[TR]
[TH]4[/TH]
[TD="bgcolor: #002060"]Date[/TD]
[TD="bgcolor: #002060"]Sales[/TD]
[TD="bgcolor: #002060"]Customer[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Unique Count[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Select From Drop-down:[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$50[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC, align: right"]6[/TD]
[TD][/TD]
[TD]Sioux[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]8/14/10[/TD]
[TD="align: right"]$150[/TD]
[TD]Sioux[/TD]
[TD][/TD]
[TD="bgcolor: #002060"]Return Unique Items[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]8/15/10[/TD]
[TD="align: right"]$211[/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]8/16/10[/TD]
[TD="align: right"]$95[/TD]
[TD]Moe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Sioux[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]8/17/10[/TD]
[TD="align: right"]$43[/TD]
[TD]Moe[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Moe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$22[/TD]
[TD]Chin[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Chin[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]8/13/10[/TD]
[TD="align: right"]$1[/TD]
[TD]YourName[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]YourName[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"]8/14/10[/TD]
[TD="align: right"]$85[/TD]
[TD]philobr[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]philobr[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




The data validation drop-down list would show this:

Joe
Sioux
Moe
Chin
YourName
philobr
Click to expand...

I find this post almost ten years later and what a great help it was. It works like a charm. Thank you mgirvin, mrexcel and the Internet!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Join multiple rows values in same cell, based on 2 conditions.
Replies
5
Views
733
etaf
etaf
C
  • Solved
Index match function
Replies
3
Views
728
Alex Blakenburg
A
N
  • Question Question
Data Validation using a SPILL range
Replies
3
Views
373
Nicole Jones
N
zoharb
  • Solved
unique() in data validation
Replies
1
Views
301
Eric W
Eric W
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom

Forum statistics

Threads
1,223,912
Messages
6,175,339
Members
452,637
Latest member
Ezio2866

Share this page

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
Back
Top