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?

 

[pbornemeier]

Use Data | Filter | Advanced Filter
Select Copy to another location
List Range = $A:$A
Copy to: $B$1
Put a check in Unique records only

 

[cadence]

Thanks, Phil I think I muddled my ambitions a little.

if column A has
column A
dog
cat
apple
orange
dog
apple
monkey

how can I make column B only display dog, cat, apple, orange monkey as unique values?

sorry for the confusion.

 

[pbornemeier]

From the menu, select Data | Filter | Advanced Filter
Select Copy to another location
Set the List range to $A:$A
Set Copy to: $B$1
Put a check in Unique records only
Click OK
This will give you the list
you want in column B - except first entry will be 'Column A' since that is a unique entry in column A

 

[ninjagrover]

Hi Cadence.

Do you want the second list to only show each value from column a once?

ie:

dog dog
cat cat
orange orange
dog
monkey monkey

 

[cadence]

Phil thanks for your patience, that worked ok, the only difficulty is that if I add any new options to column A it is not automatically updated in column B.

Ninjagrover: yes that is correct except I would want their to be no spaces where the duplicate records are removed.

dog dog
cat cat
orange orange [no second dog]
monkey monkey

 

[AdamL]

If you didn't want to use built in functionality like pivot tables and filters etc, then something like this in B1, confirmed with Ctrl+Shift+Enter, filled down:

=INDEX(A:A,SMALL(IF(ROW($A$1:$A$2000)=MATCH($A$1:$A$2000&"",$A$1:$A$2000&"",0),ROW($A$1:$A$2000),9999),ROWS(A$1:A1)),1)&""

(adapted from other threads dealing with the same issue)

Cheers
A

 

[ninjagrover]

[Can't edit 1st post.]

Cadence, I'm sure there is a better way to do this but behold the power of the IF formula:

In B1 put: =A1
In B2 put: =IF(ISNA(MATCH(A2,$B$1:B1,0))=TRUE,A2,"")

You can to fill this down to the extent of your list in column A, and it should pick up the first time a value comes up in column A, then leave a blank cell for all other instances.

- Grover.

 

[ninjagrover]

Cadence, just read post.

Hmm, you can then use the advance copy if you use Column B as the criteria, then copy to another location to get rid of the spaces. I don't know how to do it all within Column's A & B

- Grover.

 

[Aladin Akyurek]

Thanks, Phil I think I muddled my ambitions a little.

if column A has
column A
dog
cat
apple
orange
dog
apple
monkey

how can I make column B only display dog, cat, apple, orange monkey as unique values?

sorry for the confusion.

Running Advanced Filter with the Unique records only option checked would be a good option. What follows shows a formula option...

<TABLE style="WIDTH: 105pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=140 border=0 x:str><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 43pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=57 height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=83 x:num x:fmla='=SUM(IF(FREQUENCY(IF(A3:A9<>"",MATCH("~"&A3:A9,A3:A9&"",0)),ROW(A3:A9)-ROW(A3)+1),1))' x:arrayrange="B1">5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>List</TD><TD class=xl25 id=td_post_1738480 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Distinct List</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>dog</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">dog</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>cat</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">cat</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>apple</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">apple</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>orange</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">orange</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>dog</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">monkey</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>apple</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>monkey</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=""> </TD></TR></TBODY></TABLE>

List occupies A2:A9...

B1:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A3:A9<>"",MATCH("~"&A3:A9,A3:A9&"",0)),
    ROW(A3:A9)-ROW(A3)+1),1))

B3:

Control+shift+enter, not just enter...

=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($D$3:D3))),"")

...and copy down.

Although the second formula is pretty long, it's congruent with the first.