Unique Validation List

[cadence]

Hello all,

If I have in
column A
dog
cat
apple
orange
dog
apple
monkey

How Can I create a list that only selects unique values ie dog, cat, apple, orange monkey and displays in in column B

Also can it be a dynamic range so that if the list grew to 100 the validation would check all in the range?

 

[cadence]

thanks guys Adam that worked great.

Aladin, thanks the first of your formulas seemed to post the number of times each item appeared rather than just the item itself. Is that what it is meant to do, or did i make a mistake?

The second formula I almost got to work, but the last part seemed a little odd "ROWS($D$3:D3)"

what does referencing column D mean?

Could someone tell me the significance of Control+shift+enter, I've never seen that before?

cheers for all the feedback

 

[Aladin Akyurek]

thanks guys Adam that worked great.

Aladin, thanks the first of your formulas seemed to post the number of times each item appeared rather than just the item itself. Is that what it is meant to do, or did i make a mistake?

The formula in B1 determines the number of unique/distinct items.

[/quote]The second formula I almost got to work, but the last part seemed a little odd "ROWS($D$3:D3)"

what does referencing column D mean?[/quote]

That bit should have been ROWS($B$3:B3), so in full:

=IF(ROWS($B$3:B3)<=$B$1,INDEX($A$3:$A$9,SMALL(IF(FREQUENCY(IF($A$3:$A$9<>"",MATCH("~"&$A$3:$A$9&"",$A$3:$A$9&"",0)),ROW($A$3:$A$9)-ROW($A$3)+1),ROW($A$3:$A$9)-ROW($A$3)+1),ROWS($B$3:B3))),"")

Could someone tell me the significance of Control+shift+enter, I've never seen that before?

cheers for all the feedback

Look for "array formulas" in Excel's Help. Also, see:

http://www.emailoffice.com/excel/arrays-bobumlas.html

A tip: Always test the proposed formulas for robustness and speed. The former is easy to do: Insert a few rows before the data the formula attempts to process or before the formula row.

 

[cadence]

Aladin,

thanks, I was treating them as two separate ways of doing, now I understand [almost!].

I have go it to work, just one question if I change and Item in a column A, it doesn't update in Column B, If I update again, it updates the 1st change. [Weird] and Idea why it would do that?

Thanks for the insight on the Control+shift+enter [one more for the knowledge bank]

 

[cadence]

Aladin,

thanks, I was treating them as two separate ways of doing, now I understand [almost!].

I have go it to work, just one question if I change and Item in a column A, it doesn't update in Column B, If I update again, it updates the 1st change. [Weird] and Idea why it would do that?

Thanks for the insight on the Control+shift+enter [one more for the knowledge bank]

 

[Aladin Akyurek]

Aladin,

thanks, I was treating them as two separate ways of doing, now I understand [almost!].

The result of the first formula is a significant parameter for the formula that creates/builds the distinct list.

I have go it to work, just one question if I change and Item in a column A, it doesn't update in Column B, If I update again, it updates the 1st change. [Weird] and Idea why it would do that?

Is Calculation set to Automatic?

Thanks for the insight on the Control+shift+enter [one more for the knowledge bank]

Yeah, one that is necessary for interesting computations.

 

[cadence]

Yes Aladin yes it was set to automatic.

I had changed the range A3:A9 to a named range. an when I reverted it back it seemed fine?

I guess I'm fine to make it work the way it is, but if you can shed any light as to why a named range might do that, I'm prince Charles [all ears] !

regardless thanks for the perseverance.

 

[Aladin Akyurek]

Yes Aladin yes it was set to automatic.

I had changed the range A3:A9 to a named range. an when I reverted it back it seemed fine?

I guess I'm fine to make it work the way it is, but if you can shed any light as to why a named range might do that, I'm prince Charles [all ears] !

regardless thanks for the perseverance.

I'm not sure I've got the issue. If the trouble has been happening with having a named range, the following might have been the issue:

the ROW($A$3:$A$9)-ROW($A$3)+1 bit must then read:

ROW(range)-ROW(INDEX(range,1,1))+1

Note that such a use lengthens the formula, which can be a problem.