Copying of non-consecutive, columnar data into consecutive-columnarcells

[2.7182818284590...]

Hi:

I have data located at a1:a9, then a:10 is blank; then, I have 9 more
lines of data from a11:a19, and a20 is blank...

a1, a11, a21...are all the texts/numbers that I want printed on a
different page. But here's the catch: I'd like to print it at
locations a1, a2, a3...

Specifically, the data that I have appears like this on

THIS IS ON SHEET 1:
A1: prefix: Mr
A2: emailAddress: (e-mail address removed)
A3: firstName: John
A4: lastName: Smith
A5: company: ABC Company
A6:
A7: prefix: Mrs
A8: emailAddress: (e-mail address removed)
A9: firstName: Kim
A10: lastName: Davids
A11: company: XYZ Company

ON SHEET 2, I'd like to see in this format:
A1: Prefix B1: FirstName C1: LastName D1: Company
E1: eMailAddress
Mr. John Smith ABC
Company (e-mail address removed)
Mrs. kim Davids XYZ
Company (e-mail address removed)


Would it be easier if I put all the data in the same rows (not the
same columns)?

 

[Peo Sjoblom]

Select the different ranges first select a1:a9, then hold down the ctrl key
and select a11:a19, and so on while still holding down the ctrl key, press
ctrl + c to copy and ctrl + v to paste. Works for me.

--


Regards,


Peo Sjoblom

 

[2.7182818284590...]

Peo,

I must have explained my problem wrongly. Also, my formatting didn't
appear right.

A1 FirstName John
A2 LastName Smith
A3 State NY
A4
A5 FirstName David
A6 LastName Young
A7 State MA
.
.
.
A120 FirstName Ray
A121 LastName Chan
A122 State RI

This format above is undesirable for me. Instead, I'd like to
automate the format such that I see the data arranged like this:
A1 FirstName B1 LastName C1 State
John Smith MA
David Young NY
. . .
. . .
. . .
Ray Chan RI

I understand that I can individually, for each data set of 3 rows,
copy and paste special and choose "Transpose". But doing this for the
dozens of data sets is not so efficient use of my time.

How do I automate this?

 

[Max]

Assuming data below (as per your original posting) is in Sheet1's col A, in
A1 down,
where the pattern is 5 cells, an empty cell, 5 cells, an empty cell, and so
on:


Mr
(e-mail address removed)
John
Smith
ABC Company

Mrs
(e-mail address removed)
Kim
Davids
XYZ Company

etc

In Sheet2,
Put in A1: =OFFSET(Sheet1!$A$1,ROWS($1:1)*6-6+COLUMNS($A:A)-1,)
Copy across to E1, fill down as far as required to exhaust the data
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,700, Files: 356, Subscribers: 53
xdemechanik
---