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,
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)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))



"wx4usa" wrote:

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.

wx4usa;401668 Wrote:
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 !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112064

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?).
--
HTH...

Jim Thomlinson


"wx4usa" wrote:

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.

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

"wx4usa" wrote:

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.