Transposing *lots* of rows

[smartgal]

I have about 50K entries in one row that need to go from vertical to
horizontal. Each is a name, address, etc., so when I get done it should be
about 10K entries. Right now it looks like this:

Name
Address
City, ST Zip
Phone

And what I'm wanting is Name | Address | City | ST | Zip | Phone

I know I can do each row separately (copy / paste special / transpose), is
there a faster way to execute it in bulk? Thx!

 

[Gary''s Student]

Do it in two steps. Say your data in column A looks like:

John Smith
123 Maple Ave.
Springfield, NJ 081234
123-456-7890
Jane Doe
100 Center Street
Milton, DE 0123456
234-123-0987

In B1 enter:

=INDIRECT("A" & ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1))

The copy B1 to B1 thru E100 or even further. You will see:

John Smith 123 Maple Ave. Springfield, NJ 081234 123-456-7890
Jane Doe 100 Center Street Milton, DE 0123456 234-123-0987

Each 4 elements have been moved to a row. The next step is to take column D
and split it up.

 

[smartgal]

This is pulling everything up four rows but it's not making the
transposition. Here are my first 8 rows of data:

A069 A & A OPERATING INC.
NO CONTACT GIVEN
RT. 3, BOX 222
LAFAYETTE, LA 70506-0000
(318)261-0295 ()
0011 A & B OIL COMPANY
BETTY MICHNA
27125 ORTH LANE
CONROE, TX 77385-0000
(318)375-5132 ()

When I put the formula in B1, here's what my A and B columns look like now:

A069 A & A OPERATING INC. A069 A & A OPERATING INC.
NO CONTACT GIVEN (318)261-0295 ()
RT. 3, BOX 222 CONROE, TX 77385-0000
LAFAYETTE, LA 70506-0000 NO ADDRESS ON RECORD.
(318)261-0295 () CARL NOLAN
0011 A & B OIL COMPANY 0018 A & E OIL, INC.
BETTY MICHNA NO PHONE GIVEN
27125 ORTH LANE SHREVEPORT, LA 71106-0000
CONROE, TX 77385-0000 NO ADDRESS ON RECORD.
(318)375-5132 () JOHN DUFFELL
0008 A & B PARTNERSHIP 5149 A & J ROBERTSON

As you can see, it pulled the phone number from Col A to Col B but didn't
put it on row 1. I'm using 2003 SP3, might that make a difference?

Thanks so much for your help.

mm

 

[Gord Dibben]

Your recent post shows 10(not 8) rows of data, 5 cells per set.

Change the formula to =INDIRECT("A" & ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1))

Enter the formula in B1 and copy across to F1(looks like you did not do this
originally)

Select B1:F1 and copy down untill you see zeros.

Select all and copy then paste special>values>ok>esc.

Then follow GS's instructions about text to columns on E.

Insert a column to the right of E before splitting.


Gord Dibben MS Excel MVP

 

[smartgal]

PERFECTION!! Thanks *so* much!

Your recent post shows 10(not 8) rows of data, 5 cells per set.

Change the formula to =INDIRECT("A" & ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1))

Enter the formula in B1 and copy across to F1(looks like you did not do this
originally)

Select B1:F1 and copy down untill you see zeros.

Select all and copy then paste special>values>ok>esc.

Then follow GS's instructions about text to columns on E.

Insert a column to the right of E before splitting.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

thanks!
--
Gary''s Student - gsnu200778

Your recent post shows 10(not 8) rows of data, 5 cells per set.

Change the formula to =INDIRECT("A" & ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1))

Enter the formula in B1 and copy across to F1(looks like you did not do this
originally)

Select B1:F1 and copy down untill you see zeros.

Select all and copy then paste special>values>ok>esc.

Then follow GS's instructions about text to columns on E.

Insert a column to the right of E before splitting.


Gord Dibben MS Excel MVP