formula to pull names out of list

[Kim Shelton at PDC]

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

 

[RagDyer]

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),"",Sheet1!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.

 

[Dave Peterson]

Does it have to be a formula?

If no, then...

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

 

[Oldersox]

You could also use a Pivot Table