copying data from a list

[Guest]

I have a spreadsheet that I add new data too. I want to produce a list on a
seperate worksheet that takes the names from the list and puts them in a new
list, but I dont want to duplicate. ie

Abbey
Natwest
Abbey
BOS
Natwest
Halifax

So I would like the formula to look at that list and produce the following

Abbey
Natwest
BOS
Halifax

and also to update as I add new names

 

[Guest]

Take a look here:

http://www.cpearson.com/excel/ListFunctions.aspx

 

[Guest]

Thanks toppers, i didnt find th link that useful - a bit complex.

is their not a single formula that looks at a list and then takes a single
entry from the list and populates my new list without duplicating entries

 

[Ragdyer]

Say your original list is on Sheet1, Column E, from E1 to E100, and you want
to start your "unique" list on Sheet2, starting in A1.
Assume you'll size the "unique" formula for 200 rows to account for future
expansion of the original list.

In A1 of Sheet2 enter:

=Sheet1!E1

Then, in A2 enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$E$1:$E$200&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$E$1:$E$200),"",Sheet1!$E$1:$E$200),MATCH(0,COUNTIF(A$1:A1,Sheet1!$E$1:$E$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 far as needed until you see an #N/A
error, meaning you've exhausted your uniques from the original list at this
time.
As you enlarge the original list, those #N/A errors will turn into
additional unique entries.

 

[Guest]

RD - you are the King. Thanks for your help

Say your original list is on Sheet1, Column E, from E1 to E100, and you want
to start your "unique" list on Sheet2, starting in A1.
Assume you'll size the "unique" formula for 200 rows to account for future
expansion of the original list.

In A1 of Sheet2 enter:

=Sheet1!E1

Then, in A2 enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$E$1:$E$200&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$E$1:$E$200),"",Sheet1!$E$1:$E$200),MATCH(0,COUNTIF(A$1:A1,Sheet1!$E$1:$E$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 far as needed until you see an #N/A
error, meaning you've exhausted your uniques from the original list at this
time.
As you enlarge the original list, those #N/A errors will turn into
additional unique entries.

 

[Ragdyer]

You're welcome, and appreciate the feed-back.