Create list of unique entries for use in validation

R

raphiel2063

Is there a function/formula I can put into a series of cells which can look
at a list (with repeat entries) and simply return the unique one.

E.g.

Tomato
Tomato
Banana
Tomato
Pear
Banana
Apple
Pear
Apple
Tomato

Would be displayed as

Tomato
Banana
Pear
Apple

Then I can use Validation on this 'filtered' selection to create a drop down
selection box.
 
R

RagDyeR

Say your original list is in Column A, starting in A1.

In B1 enter,
=A1
In B2, enter this *array* formula:
=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK($A­$
1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)))


--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


*After* the CSE entry, copy down until you get #N/A errors.
This tells you that you have exhausted the unique numbers in Column A.


I have sized the formula to A200.
You can revise that as necessary.


As you add additional values to Column A, those errors will change to
display additional uniques.

You can then remove the formulas, leaving just the data behind by ...
selecting the list in Column B,

Right click in the selection and choose "Copy",

Right click again, and choose "Paste Special",

Click on <Values>, then <OK>, then <Esc>.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Is there a function/formula I can put into a series of cells which can look
at a list (with repeat entries) and simply return the unique one.

E.g.

Tomato
Tomato
Banana
Tomato
Pear
Banana
Apple
Pear
Apple
Tomato

Would be displayed as

Tomato
Banana
Pear
Apple

Then I can use Validation on this 'filtered' selection to create a drop down
selection box.
 
R

ryguy7272

Try this:

=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL(IF(A1:A20<>"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")

This is a CSE function (Ctrl + Shift + Enter)
 
R

ryguy7272

Uh, sorry, that function that I posted removes blank spaces...
Try this (I got it on this DG a while back):

Sub Uniques()
Dim i As Integer
i = 1
Do Until Cells(i, 1).Value = "" '(as long as your data is in column 1)
If Cells(i, 1) = Cells(i + 1, 1) Then
Else
Cells(i, 1).Copy
Cells(i, 5).PasteSpecial xlValues '(this pastes into column E)
End If
i = i + 1

Loop
Range("E5:E1000").Sort Key1:=Range("E5"), Order1:=xlAscending

Columns("E:E").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending
Range("A1").Select

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top