HELP....Is there a way to automate copy/paste special/transpose -

[Mel]

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

 

[Max]

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

 

[Mel]

Thank you very much Max!!!

One issue...it seems to be skipping every other contact...
I get this...
I am missing...all the way down the list...any ideas what I did wrong?
Thanks again!!!

 

[Max]

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 <at>yahoo<dot>com
----

Thank you very much Max!!!

One issue...it seems to be skipping every other contact...

I get this...

1234567892 / 1234567892> > email address3

I am missing...all the way down the list...any ideas what I did wrong?

1234567892 / 1234567892> > email address2

 

[Mel]

YAHOO!!! MY FAULT!!!

How about this question Yoda...

First column with names...need to sep. into 2 columns
EX.

LastName, Max

Max>>LastName, etc.

Thank you so much, you are a life saver!!!

 

[Max]

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

 

[Mel]

Once again...thank you for all your help!

Regards,

Melinda

 

[Max]

You're welcome !
Thanks for the feedback ..