You need an array formula to return unique values.
This one assumes that the list in column A of Sheet1 does not go down any further than row 1000 and is filled down to row 30 on Sheet2.
It uses the MATCH function and so is not case sensitive so if "a" and "A" are both in the Sheet1 list you will only see an "a" or an "A" in the list of unique values not both. The one you see in the list of unique values is the first one to appear in the Sheet1 list.
The array formula in Sheet2 A1 is...
=IF(ROW($A1)>ROUND(SUMPRODUCT((Sheet1!$A$1:$A$1000<>"")/(COUNTIF(Sheet1!$A$1:$A$1000,Sheet1!$A$1:$A$1000)+(Sheet1!$A$1:$A$1000=""))),0),"",INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(ISNA(MATCH(Sheet1!$A$1:$A$1000,Sheet1!$A$1:$A$1000,0)),"",IF(MATCH(Sheet1!$A$1:$A$1000,Sheet1!$A$1:$A$1000,0)=ROW($A$1:$A$1000),ROW($A$1:$A$1000)-MIN(ROW($A$1:$A$1000))+1,"")),ROW($A1))))
It is a single-valued array formula that must be entered using the Ctrl+Shift+Enter key combination and has been filled down to row 30 in the attached doc.
Beau Nydal
Bookmarks