Insert 2 rows before the current row such that A3:A10 comes house the
sample you provided.

In A2 enter: Item (or some such)

In B2 enter: 0 (This 0 is required.)

In B3 enter & copy down:

=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

Note that this gives you a count of unique/distinct items in A.

In C2 enter: D-List (from Distinct List, or some such)

In *C3* enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),"")

Henrik wrote:
Hi,

I am interested in developing a worksheet formula that list each unique
observation from a dataset as it is dragged down. I am aware that both
autofilter and pivot tables are capable of doing this, but I want an actual
worksheet function.

For instance, I have the following dataset (starting in cell A1):

Apple
Banana
Orange
Apple
Apple
Banana
Pear
Mango

As the function is dragged down (starting with cell C1), it would return the
following observations (does not have to be in this order):

Apple
Banana
Orange
Pear
Mango

I have previously develoed the following worksheet function to count unique
observations:

=SUMPRODUCT((A1:A8<"")/(COUNTIF(A1:A8,A1:A8)+(A1:A8="")))

(the function returns 5)

Your help is greatly appreciated!


Thanks,
Henrik