Extract Unique entries in a column
Let column A from A3 on house the data with a label in A2.
In B1 enter: 0
In B2 enter: Idx
In B3 enter & copy down:
=IF(A3<"",IF(ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.9 9999999999999E+307,$B$1:B2)+1,""),"")
In D1 enter:
=LOOKUP(9.99999999999999E+307,B:B)
which also gives you a count of distinct/unique items.
In D2 enter: Distinct List
In D3 enter & copy down:
=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,$B$3:$B$65536,$A$3:$A$65536),"")
Another option is Advanced Filter, which is already suggested in this
thread.
Jeff wrote:
is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.
I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.
I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.
Thanks
Jeff
|