If I have a column of names (BUT the names are repeated) in a listed and I
want to have a formula that pulls out each name 1 time and puts it in a list
on a different sheet how do I do this.

Example WorkSheet 1
ABC Company
ABC Company
DEF Company
ETC. down the list.

I want to end up with 1 name on 1 line for each company on another worksheet.

Example Worksheet 2
ABC COmpany
DEF Company
ETC

Say your list started in A1 of Sheet1, and went to A200.

In say B1 of Sheet2, enter:
=Sheet1!A1

Then in B2 of Sheet2, enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,Sheet1!A$1:A$200& ""),0)),"",INDEX(IF(ISBLANK(Sheet1!A$1:A$200),"",S heet1!A$1:A$200),MATCH(0,COUNTIF(B$1:B1,Sheet1!A$1 :A$200&""),0)))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Kim Shelton at PDC" wrote in
message ...
If I have a column of names (BUT the names are repeated) in a listed and I
want to have a formula that pulls out each name 1 time and puts it in a
list
on a different sheet how do I do this.

Example WorkSheet 1
ABC Company
ABC Company
DEF Company
ETC. down the list.

I want to end up with 1 name on 1 line for each company on another
worksheet.

Example Worksheet 2
ABC COmpany
DEF Company
ETC

Does it have to be a formula?

If no, then...

Debra Dalgleish explains it he
http://www.contextures.com/xladvfilter01.html#FilterUR

Kim Shelton at PDC wrote:

If I have a column of names (BUT the names are repeated) in a listed and I
want to have a formula that pulls out each name 1 time and puts it in a list
on a different sheet how do I do this.

Example WorkSheet 1
ABC Company
ABC Company
DEF Company
ETC. down the list.

I want to end up with 1 name on 1 line for each company on another worksheet.

Example Worksheet 2
ABC COmpany
DEF Company
ETC

--

Dave Peterson

You could also use a Pivot Table