PASTE SPECIAL SOLUTION NEEDED

[Guest]

I HAVE A FILE THAT I COPIED INTO EXCEL FROM A .LST FILE WHICH I NEED TO
CONVERT FROM A SINGLE COLUMN SECTION TO ROWS. I CAN DO THIS USING PASTE
SPECIAL HOWEVER I CAN ONLY SEE A WAY TO DO THIS BY GOING TO EACH INDIVIDUAL
CONVERSION WHICH WOULD TAKE 4EVER AS THE SHEET IS 119 PAGES LONG. IF YOU KNOW
OF A WAY, PLZ HELP

EXAMPLE -

COLUMN A

NAME
SS #
DOB
ADDRESS1
ADDRESS2
PHONE

AND I NEED IT AS SUCH

LINE 1 COL. A COL. B COL.C COL.D COL.E COL.F
NAME DOB SS# ADDR1 ADDR2 PH #

 

[Max]

Please do not use all caps in your post.
It's tough to read and considered impolite ..

Ok, let's assume the sample data below
is in Sheet1, in col A, A1 down

The data is assumed to be in groups of 6 lines each
with a blank row in-between each group, viz.:

1NAME
1SS #
1DOB
1ADDRESS1
1ADDRESS2
1PHONE

2NAME
2SS #
2DOB
2ADDRESS1
2ADDRESS2
2PHONE

etc

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

Copy A1 across to F1,
then copy down until zeros appear
signalling exhaustion of data

The above will extract the data from col A in Sheet1
and re-lay it nicely in a column-wise manner in Sheet2
viz. :

1NAME 1SS # 1DOB 1ADDRESS1 1ADDRESS2 1PHONE
2NAME 2SS # 2DOB 2ADDRESS1 2ADDRESS2 2PHONE
etc

 

[Max]

And if necessary, just kill the formulas in Sheet2 thereafter
by copying the cols A to F and then paste special as "Values" in-place