How can I get a unique list of a column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yabi - 10 Apr 2007 08:36 GMT

I have a column which is the name of cities. There are about 5 cities
which is repeated in 50 records (rows). I want to get the name of this
5 cities in a separate column. How can I do this?

TIA
Rasoul Khoshravan

Reply to this Message

Bob Phillips - 10 Apr 2007 08:52 GMT

Use Data>Filter>Advanced Filter. There is an option to filter unique
records.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a column which is the name of cities. There are about 5 cities
> which is repeated in 50 records (rows). I want to get the name of this
> 5 cities in a separate column. How can I do this?
>
> TIA
> Rasoul Khoshravan

Reply to this Message

Max - 11 Apr 2007 11:01 GMT

A slightly different take on your post ..

Read it that what you want is a uniques listing of the duplicates
(ie excluding items which appear only once in the source list)

Assume source data running in A2 down

Place in B2:E2
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
=IF(ROW(A1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW(A1))))
=IF(C2="","",IF(COUNTIF(A:A,C2)>1,ROW(),""))
=IF(ROW(A1)>COUNT(D:D),"",INDEX(C:C,SMALL(D:D,ROW(A1))))
(Leave B1 & D1 blank)

Select B2:E2, copy down to the last row of source data. Col E will return
the uniques listing of the duplicates, neatly bunched at the top. [Col C
returns the "full" uniques list, same as what you'd get when you run
Advanced Filter > Uniques]

Signature

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

Reply to this Message

Rate this thread: