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?

 

[smittyssluggers]

EXCELlent! I am glad that it worked.

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?

 

[smittyssluggers]

No takers on my question?

 

[Aladin Akyurek]

No takers on my question?

Care to post a small sample?

 

[smittyssluggers]

Care to post a small sample?

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.

 

[Aladin Akyurek]

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?

 

[smittyssluggers]

Yes, except the original post used the array as "Customer", I am using it as "ItemList" and it starts at J24 and following.

 

[Aladin Akyurek]

Yes, except the original post used the array as "Customer", I am using it as "ItemList" and it starts at J24 and following.

Define Ivec using Formulas | Name Manager as referring to:

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

J24, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(ItemList,SMALL(IF(FREQUENCY(IF(ItemList<>"",
  MATCH(ItemList,ItemList,0)),Ivec),Ivec),ROWS($J$24:J24))),"")

 

[Zorod]

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.

 

[Aladin Akyurek]

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.

Sheet1

Row\Col	A​	B​	C​
4​	Date	Sales	Customer
6​	8/14/2010​	$150 ​	Sioux
7​	8/15/2010​	$211 ​	Joe
8​	8/16/2010​	$95 ​	Moe
12​	8/14/2010​	$85 ​	philobr
13​
14​
15​	4​
16​	#List#
17​	Joe
18​	Sioux
19​	Moe
20​	philobr
21​

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

Ivec is defined as referring to:

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

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

=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:

=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:

=OFFSET(Sheet1!$A$17,0,0,Sheet1!$A$15)

which you can use as Source in data validation if you will.

 

[Kondar]

Here is an idea for a formula solution.

If you had this data list:

Sheet1

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

<tbody>
</tbody>

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

	E
4	Unique Count
5	5

<tbody>
</tbody>

Array Formulas Cell Formula E5 =SUM(IF(FREQUENCY(IF(Customers<>"",MATCH("~"&Customers,Customers&"",0)),ROW(Customers)-ROW(C5)+1),1)) <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

<tbody>
</tbody>

Worksheet Defined Names Name Refers To Sheet1!Customers =Sheet1!$C$5:INDEX(Sheet1!$C$5:$C$19,COUNTA(Sheet1!$C$5:$C$19)) <tbody> </tbody>

<tbody>
</tbody>

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

Sheet1

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

<tbody>
</tbody>

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

	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

<tbody>
</tbody>

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

Sheet1

	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

<tbody>
</tbody>

The data validation drop-down list would show this:

Joe
Sioux
Moe
Chin
YourName
philobr

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!