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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
  • Question Question
Choose from List Box
Replies
5
Views
183
ddnron
D
D
  • Solved
Join multiple rows values in same cell, based on 2 conditions.
Replies
5
Views
740
etaf
etaf
C
  • Solved
Index match function
Replies
3
Views
747
Alex Blakenburg
A
N
  • Question Question
Data Validation using a SPILL range
Replies
3
Views
606
Nicole Jones
N
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
206
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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