Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Data List to Different Worksheet | Excel Discussion (Misc queries) | |||
copying list items | Excel Discussion (Misc queries) | |||
Copying to an autofiltered list | Excel Discussion (Misc queries) | |||
Copying list-box control | Excel Discussion (Misc queries) | |||
Copying from list | Excel Worksheet Functions |