Create value list from a range of values

M

mallets123

I've got a range of cells containing state abbrev. that I want to lis
in another range excluding all duplicated states abbrev's.

Is there a way to list the non-duplicated values automatically with
function?

I've tried using the excel Data Filter menu but it seems that needs t
be performed manually. I need a function that automatically calculate
when there is a change or addition to my list of states.

Thanks for your help..
 
M

Max

One way, using non-array formulas

Assume source data is in A1 down

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW())),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Select B1:C1, fill down to say, C100,
to cover the max expected extent of source data

Col B will auto-return the list of unique items within col A,
with all unique items neatly bunched at the top
 
Top