How can I get a unique list of a column?

[Yabi]

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

 

[Bob Phillips]

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

--
HTH

Bob

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

 

[Max]

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),"",INDEX(C:C,SMALL(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]