Return Just the Unique Entries From a Range of Cells

[wx4usa]

I have a range of 1000 cells..A1:1000. This range holds the favorite
restaurant list as provided by our customers. So, in each cell is a
restaurant name. There are approximately 50+/- unique restaurant names
in these 1000 cells. Many are obviously repeated since different
customers say the rest is their favorite too.

How do I extract this list of the 50+/- unique restaurants mentioned
in the range of 1000 cells? and place these unique names in B1:50?

I need the unique list so that I can then count the number of
occurrences of each unique name and then rank them.

 

[Eduardo]

Hi,
In column B enter
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

and in column C to remove the blank spaces

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

 

[Pecoflyer]

I have a range of 1000 cells..A1:1000. This range holds the favorite
restaurant list as provided by our customers. So, in each cell is a
restaurant name. There are approximately 50+/- unique restaurant names
in these 1000 cells. Many are obviously repeated since different
customers say the rest is their favorite too.

How do I extract this list of the 50+/- unique restaurants mentioned
in the range of 1000 cells? and place these unique names in B1:50?

I need the unique list so that I can then count the number of
occurrences of each unique name and then rank them.

Hi,
try data - Filter - Advanced filter
Check " copy to another location"
Fill in the location
Select " Unique records only"

OK


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !

 

[Jim Thomlinson]

Based on your description of the problem I would be inlcined to use a pivot
table. Select Data -> Pivot Table. Choses your data area and then add the
restaurant names to the left column. Also add the Restraurant names to te
center data area. You will now get a list of the unique restraunta along with
a count of the number of occurances. Right click the Restaurants and select
Field Settings -> Advanced. Change the sort option and you can sort based on
the count (or show the top?).

 

[PJ]

Another possibility:

In B1: =A1

In B2:
=IF(ISERROR(MATCH(0,COUNTIF($B$1:B1,$A$1:$A$1000&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$1000),"",$A$1:$A$1000),MATCH(0,COUNTIF($B$1:B1,$A$1:$A$1000&""),0)))

This is an array formula, entered using Ctrl-Shift-Enter

Copy down as far as needed