Converting a large vertical mailing list into a horizontal format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin VanHalen - 30 Apr 2005 05:33 GMT

I've copied a vertical list of over 1000 customers ID #'s, names and
addresses and pasted the info into column A in Excel. This info will be
transferred to a letter for each customer. For this to work effeciently the
customer info needs to be sorted horizontally in specific columns.

Example: A B C D
98-2004 John Doe 5555 Road Lane Town, State 77777

I know how to do these individually, but what is the method to do this more
quickly?
Thank you so much for your help!

Reply to this Message

Max - 30 Apr 2005 12:23 GMT

No harm giving this a try ..

Assume the mailing list is all in groups of 4 lines (w/o any blank lines in
between groups) in Sheet1, from A1 down (sample below)

98-2004
John Doe
5555 Road
Town, State 11111
98-2005
Peter Taylor
1111 Lane
Town, State 22222
100-2004
Mary Dees
8888 Road Lane
Town, State 88888
etc

In Sheet2
-----------
Put in A1:=OFFSET(Sheet1!$A$1,ROW()*4-4+COLUMN()-1,)

Copy A1 across to D1, fill down until zeros appear signalling exhaustion of
data from Sheet1

This'll return the list from Sheet1, placing each line per group of 4 lines
nicely into cols A to D as required

Freeze the values in cols A to D with a copy > paste special > values either
in-place or elsewhere

Signature

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> I've copied a vertical list of over 1000 customers ID #'s, names and
> addresses and pasted the info into column A in Excel. This info will be
[quoted text clipped - 7 lines]
> quickly?
> Thank you so much for your help!

Reply to this Message