list with multiple repeated data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ArturoCohen - 14 Aug 2006 20:14 GMT

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

Reply to this Message

Dave Peterson - 14 Aug 2006 20:48 GMT

Data|Filter|Advanced filter
Check unique records only.

Debra Dalgleish explains it in more detail:
http://www.contextures.com/xladvfilter01.html#FilterUR

> 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
[quoted text clipped - 30 lines]
>
> Arturo

Signature

Dave Peterson

Reply to this Message

Max - 04 Oct 2006 08:01 GMT

> .. 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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 30 lines]
>
> Arturo

Reply to this Message