Create list of unique entries for use in validation
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)
--
RyGuy
"raphiel2063" wrote:
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.
|