|
HELP....Is there a way to automate copy/paste special/transpose -
Hello!
I have one column of names, address, etc. 6 rows per person...so, every sixth row is a new person/contact. I can go down contact by contact and copy/paste special/transpose and paste across the board so that each contact info is separated into it's own column...but there are several hundred contacts...is there a way to automate this so that it checks the web/query...paste the new contacts at bottom of list but in columns not rows.. EX. My name Company address1 address2 work / fax: 1234567890 / 1234567890 email address new name2 company2 address12 address22 work / fax: 1234567892 / 1234567892 email address2 ETC.... What it should be is A B C D E F E My name Company address1 address2 work / fax: 1234567890 / new name2 company2 address12 address22 work / fax: 1234567892 / F G 1234567892 email address2 AND WITH THE WEB QUERY IT WILL AUTOMATICALLY UPDATE AT THE END OF THE LIST/ROW... Any suggestions would be great!!! Do not want to have to do this manually... Thanks! 1234567890 email address |
One way
Assuming data is in col A, A1 down, in groups of 6 lines each (as posted) Put in B1: =OFFSET($A$1,ROWS($A$1:A1)*6-6+COLUMNS($A$1:A1)-1,) Copy B1 across to G1, fill down until zeros appear signalling exhaustion of data -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mel" wrote in message ... Hello! I have one column of names, address, etc. 6 rows per person...so, every sixth row is a new person/contact. I can go down contact by contact and copy/paste special/transpose and paste across the board so that each contact info is separated into it's own column...but there are several hundred contacts...is there a way to automate this so that it checks the web/query...paste the new contacts at bottom of list but in columns not rows.. EX. My name Company address1 address2 work / fax: 1234567890 / 1234567890 email address new name2 company2 address12 address22 work / fax: 1234567892 / 1234567892 email address2 ETC.... What it should be is A B C D E F E My name Company address1 address2 work / fax: 1234567890 / new name2 company2 address12 address22 work / fax: 1234567892 / F G 1234567892 email address2 AND WITH THE WEB QUERY IT WILL AUTOMATICALLY UPDATE AT THE END OF THE LIST/ROW... Any suggestions would be great!!! Do not want to have to do this manually... Thanks! 1234567890 email address |
Thank you very much Max!!!
One issue...it seems to be skipping every other contact... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I get this... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I am missing...all the way down the list...any ideas what I did wrong? new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 Thanks again!!! "Max" wrote: One way Assuming data is in col A, A1 down, in groups of 6 lines each (as posted) Put in B1: =OFFSET($A$1,ROWS($A$1:A1)*6-6+COLUMNS($A$1:A1)-1,) Copy B1 across to G1, fill down until zeros appear signalling exhaustion of data -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mel" wrote in message ... Hello! I have one column of names, address, etc. 6 rows per person...so, every sixth row is a new person/contact. I can go down contact by contact and copy/paste special/transpose and paste across the board so that each contact info is separated into it's own column...but there are several hundred contacts...is there a way to automate this so that it checks the web/query...paste the new contacts at bottom of list but in columns not rows.. EX. My name Company address1 address2 work / fax: 1234567890 / 1234567890 email address new name2 company2 address12 address22 work / fax: 1234567892 / 1234567892 email address2 ETC.... What it should be is A B C D E F E My name Company address1 address2 work / fax: 1234567890 / new name2 company2 address12 address22 work / fax: 1234567892 / F G 1234567892 email address2 AND WITH THE WEB QUERY IT WILL AUTOMATICALLY UPDATE AT THE END OF THE LIST/ROW... Any suggestions would be great!!! Do not want to have to do this manually... Thanks! 1234567890 email address |
One issue...it seems to be skipping every other contact...
Strange, as it seems to work ok here, tested on the sample data assumed in A1:A18 In A1: My name1 ..... In A18: email address3 Maybe try again ? If it still skips, email me a sample book and I'll take a look -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mel" wrote in message ... Thank you very much Max!!! One issue...it seems to be skipping every other contact... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I get this... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I am missing...all the way down the list...any ideas what I did wrong? new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 Thanks again!!! |
YAHOO!!! MY FAULT!!!
How about this question Yoda... First column with names...need to sep. into 2 columns EX. LastName, Max MaxLastName, etc. Thank you so much, you are a life saver!!! "Max" wrote: One issue...it seems to be skipping every other contact... Strange, as it seems to work ok here, tested on the sample data assumed in A1:A18 In A1: My name1 ..... In A18: email address3 Maybe try again ? If it still skips, email me a sample book and I'll take a look -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mel" wrote in message ... Thank you very much Max!!! One issue...it seems to be skipping every other contact... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I get this... My name1 Company1 address1 address1 work / fax: 1234567890 / 1234567890 email address1 new name3 company3 address3 address3 work / fax: 1234567892 / 1234567892 email address3 I am missing...all the way down the list...any ideas what I did wrong? new name2 company2 address2 address2 work / fax: 1234567892 / 1234567892 email address2 Thanks again!!! |
Glad to hear you got it to work!
First column with names...need to sep. into 2 columns EX. LastName, Max If we're doing this as a continuation of the earlier re-arrangement into cols, we've got to either kill the formulas in col A (in-situ copy paste special as values) or work on a copied pasted special as values in another sheet One way to try for the split of col A into 2 cols Insert a new empty col next to col A (this is to receive the right half of the split data) Select col A Click Data Text to Columns Next (Delimited will be selected) In step 2, Enter a comma in the box for "Other" Click Finish -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Once again...thank you for all your help!
Regards, Melinda "Max" wrote: Glad to hear you got it to work! First column with names...need to sep. into 2 columns EX. LastName, Max If we're doing this as a continuation of the earlier re-arrangement into cols, we've got to either kill the formulas in col A (in-situ copy paste special as values) or work on a copied pasted special as values in another sheet One way to try for the split of col A into 2 cols Insert a new empty col next to col A (this is to receive the right half of the split data) Select col A Click Data Text to Columns Next (Delimited will be selected) In step 2, Enter a comma in the box for "Other" Click Finish -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
You're welcome !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Mel" wrote in message ... Once again...thank you for all your help! Regards, Melinda |
| All times are GMT +1. The time now is 04:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com