list with multiple repeated data

A

ArturoCohen

Hi, i have a column with a list of numers and letters, unsorted. the
data in this column can be repeated, it goes like this

Col1
054T
034K
056L
045L
045N
054T
076H
034K
045L

I Need a way that this codes are copied into another column (col2) but
only once. if the code is 2 or more times in col1 it should be only
once in col2. is there a formula that allows me to do this on the fly
wothout recording any macro? the result shoul look like this:

Col2
054T
034K
056L
045L
045N
076H

all the codes are in col1, but they appear only once in Col2

Sorry to bother.

Thanks for all

Arturo
 
M

Max

.. is there a formula that allows me to do this on the fly

Here's an option using non-array formulas
which will extract the uniques list dynamically ..

Assume source data (ie col 1) is running in B1 down
Put in say, J1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Put in K1:
=IF(ROW(A1)>COUNT(J:J),"",INDEX(B:B,MATCH(SMALL(J:J,ROW(A1)),J:J,0)))

Select J1:K1, copy down just enough to cover the max expected extent of
source data in col B, say down to K1000? Hide away col J. Col K will return
the required dynamic uniques list from col B, with all results neatly bunched
at the top.
 
Top