copying data from a list
 

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

copying data from a list
 

Take a look he

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

"Mark R" wrote:

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

copying data from a list
 

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

"Toppers" wrote:

Take a look he

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

"Mark R" wrote:

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

copying data from a list
 

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$20 0&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$E$1:$E$200), "",Sheet1!$E$1:$E$200),MATCH(0,COUNTIF(A$1:A1,Shee t1!$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.
--
HTH,

RD

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

"Mark R" wrote in message
...
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

"Toppers" wrote:

Take a look he

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

"Mark R" wrote:

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

copying data from a list
 

RD - you are the King. Thanks for your help

"Ragdyer" wrote:

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$20 0&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$E$1:$E$200), "",Sheet1!$E$1:$E$200),MATCH(0,COUNTIF(A$1:A1,Shee t1!$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.
--
HTH,

RD

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

"Mark R" wrote in message
...
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

"Toppers" wrote:

Take a look he

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

"Mark R" wrote:

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

copying data from a list
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mark R" wrote in message
...
RD - you are the King. Thanks for your help

"Ragdyer" wrote:

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$20 0&""),0)),"",INDEX(IF(ISBLANK(Sheet1!$E$1:$E$200), "",Sheet1!$E$1:$E$200),MATCH(0,COUNTIF(A$1:A1,Shee t1!$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.
--
HTH,

RD

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

"Mark R" wrote in message
...
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

"Toppers" wrote:

Take a look he

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

"Mark R" wrote:

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