Excel 2000 - Convert Rows to Columns 2

[dsmith910]

Hi

I've just inherited yet another spreadsheet! This one (besides other data) has addresses stored in blocks of 5 rows - I want to convert these blocks of address data to 5 columns, i.e. A1 would be Add1, A2 would become B1 (Add2), A3 would become C1 (Add2)etc.

Is there a quick way I can get Excel to move the data for me? There's 600 columns on the first sheet alone and I expect to inherit more of these sheets in due course. Therefore moving the data by hand will be pretty tedious.

Thanks

Doreen

 

[xlbo]

Are they always in blocks of 5 ??
if so then this should do the job

put the data in columnA with no headers and run this:
it'll rearrange the data onto "sheet2"

Sub ReArrange()
Dim lRow As Long, sht As Worksheet, y As Long
Set sht = Sheets("Sheet2&quot
lRow = Range("A65536&quot.End(xlUp).Row
y = 2
For i = 1 To lRow Step 5
For x = 0 To 4
sht.Cells(y, x + 1).Value = Cells(i + x, 1).Text
Next x
y = y + 1
Next i
End Sub


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[acron]

Take a look at the PasteSpecial, Transpose option.


If you copy the data you can paste special and click on the transpose checkbox to convert rows to columns.

600 columns on the first sheet ??

I thought a worksheet had a maximum of 256 columns.

A.C.

 

[KenWright]

The transpose function will not work in this instance as the OP is not looking to transpose the whole column from a column to a row. He has already been given a VBA solution, but just to complement that, here is a formula solution as well.

With all your data in Col A (No header), put the following in B1 and then if you have 600 rows copy it to B1:F120

=OFFSET($A$1,ROW()*5-(7-COLUMN(B1)),0)

Just a picky note re the code solution - I think a version independent solution is better than hardcoding wherever possible, eg

lRow = Range("A" & Rows.Count).End(xlUp).Row

instead of

lRow = Range("A65536&quot.End(xlUp).Row

This guarantees that it won't bomb just because you have only 16K rows or if they incorporate more rows* in the next release (*Yeah righttt he says).

Regards
Ken.................

 

[dsmith910]

Hi Guys

Many thanks for the help - Geoff's solution worked great, although my predecessor threw me a bit by putting one or two addresses on only 4 rows and a few more on 6!! - but this took only a moment or two to correct.

Sorry for putting 600 columns in my query - as you may have guessed I meant 600 rows!

Doubtless its me but I couldn't get Ken's solution to work - I'll have to sit down and try and figure out what I did wrong!

Thanks again

Doreen

 

[KenWright]

Should work fine - I just tried quickly again taking it straight from this post and pasting into the workbook and works OK

If I put 1,2,3 in cells A1, A2 etc down as far as you like, and then paste that formula into cell B1, and then copy it to B1:F100 say, you should then see:-

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25 and so on.

It would bomb if you had 4 or 6 rows anywhere though

Regards
Ken..............

 

[KenWright]

Should work fine - I just tried quickly again taking it straight from this post and pasting into the workbook and works OK

If I put 1,2,3 in cells A1, A2 etc down as far as you like, and then paste that formula into cell B1, and then copy it to B1:F100 say, you should then see:-

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25 and so on.

It would bomb if you had 4 or 6 rows anywhere though. You would also have to select all teh data afterwards and do Edit / Copy then Edit / Paste Special / values to hardcode the data before you then deleted Col A.

Regards
Ken..............

 

[dsmith910]

Hi Ken

Not sure what I did wrong the first time around - but have just retried your solution and it works great. I must have transposed something last time as, the addresses I need to put in columns are not actually in Column A. No doubt I jumbled something in my haste to try it out. Sorry!

Thanks again

Doreen

 

[KenWright]

A different column would have made it bomb. The 'COLUMN' bit in the formula simply saved me having to change the offset number for the formula in each column as follows:-

=OFFSET($A$1,ROW()*0,0)
=OFFSET($A$1,ROW()*1,0)
=OFFSET($A$1,ROW()*2,0)
=OFFSET($A$1,ROW()*3,0)
=OFFSET($A$1,ROW()*4,0)

These would probably have worked wherever you put them

Regards
Ken.............

 

[dsmith910]

Hi Ken

Many thanks! - have made a note of this for future use as its bound to come up again one of these days!

Doreen

 

[dsmith910]

Hi Geoff

Re your solution above - which converts several rows to columns. Is there something I can change to make it do the reverse? i.e. put columns to rows? Also is it possible to put something 'clever' in so that it will put every other column into rows in two columns, i.e. A1, C1, E1 etc. (ad nauseum) goes to the next sheet and becomes A1, A2, A3 etc. the data in B1, D1, F1 etc. will go into B1, B2, B3.

I have 4 separate sheets each with two rows (at different points on each sheet!) of about 60 columns. Each single row of 60 columns needs to become two sets of 30 rows. Unfortunately the data on the sheets is calculated from 'elsewhere' and doesn't always end up in the same row every time! If I can I'd like to convert alternate columns to rows, then copy and paste to the appropriate sheet. Since neither set of data ever seems to be in the same place on the respective sheets it would be difficult to do this automatically.

This particular set of inherited sheets isdriving me mad - once I work out what goes where and why I will be trying to make them 'grow up' into a nice database - the previous owner typed the same figures in at least half a dozen times - to my mind a pointless time wasting exercise since it has to be done at least once a month for ever and a day.

Yours hopefully,

Doreen

 

[suzi3945]

Quick followup to this question.

I have a quick followup to the question.

I just downloaded a file from the web/ in fact a name address listing and it is in the following format.


Name
Address
City, State, ZIP
Phone

Name
Address
City, State ZiP
Phone

Name
Address
City, State, ZIP
PHone.

Ihave about 1500 rows of about four lines each.

How can I transpose it to columns as follows

Name Address City State , Zip Phone
Name Address City State , Zip Phone
Name Address City State , Zip PHone

Thanks for your help

Bill McCabe

 

[KenWright]

With your data in Col A, starting A1, and assuming your data is in Blocks of 4 with NO blank lines between the blocks, then in cell B1 put the following formula:-

=OFFSET($A$1,ROW()*4-(6-COLUMN(B1)),0)

and copy across and down to B1:E400

IF, you have a blank line between each block of 4 cells, then make the formula:-

=OFFSET($A$1,ROW()*5-(7-COLUMN(B1)),0)

and copy to B1:E300

You MUST use the cells I have indicated. When done, simply copy the lot and paste special as values.

Regards
Ken...................

 

[suzi3945]

Ken:

Thanks for your help;.. I think I got it but it seems to go off after a number of rows...

It worked fine until the name Crosser and then the data was off one.. In other words the far left column contained address and not name..

Any ideas on that... It seems like I might have to edit it or do it in smaller batches..

Bill
'


Aardsma, Dirk E PO Box 66154 St Petersburg, FL 33736 0
Abrams, Logan Sharpe 950 Broadway Denver, CO 80203 303-813-7737
Adams, David James 2315 Briargate Pkwy Ste 100 Colorado Springs, CO 80920 719-260-7477
Combs, Tia A 2012 Shoreline Court Windsor, CO 80550 0
Commins, Brian Lee 332 Court St PO Box 400 Imperial, NE 69033 308-882-5888
Coombs, J. Curtis 14980 Crooked Spur Lane Colorado Springs, CO 80921-2417 0
Cordes, Edward B 4949 S Syracuse Ste 375 Denver, CO 80237 303-721-8755
Costello, Janet N. 501 S Cherry St Ste 570 Denver, CO 80246 303-388-1879
Couse, Nancy Lee 7861 W. 38th Ave Wheat Ridge, CO 80033 303-420-4900
Crespin, Janet Ann 2102 62nd Ave Greeley, CO 80634 970-330-3852
Crosby, Harold Gordon 12221 Beach Blvd. Apt. 1Q Garden Grove, CA 92841 0
Crosier, Leah F 2000 S Colorado Blvd Ste 2-620 Denver, CO 80222 303-785-7900
Cross, Cynthia L 5500 South Quebec Street Greenwood Village, CO 80111 0
Crosser, Bonnie L. 9800 Mt Pyramid Ct Ste 300 Englewood, CO 80112 0
Crussell, Fred Thomas 2999 Cty Rd 59 Guffey, CO 80820 Cummings, James R
2552 E Alameda Ave Apt 39 Denver, CO 80209 0 Curtis, William R
1801 California St Denver, CO 80202 303-896-5005 Czerwinski, Ray Charles
11183 W Geddes Ave Littleton, CO 80127 0 Daggs, James K.
715 W Main St Ste 101 Aspen, CO 81611-1659 970-925-4290 Dallas, Jensen Randall
PO Box 621561 Littleton, CO 80162 303-324-1593 Davidson, James W.
555 17th St Ste 3200 Denver, CO 80202 303-295-8000 Davidson, Abby Lynn
2101 S University Blvd Ste 355 Denver, CO 80208 303-871-3337 Davis, Anne Marie
1675 Broadway Denver, CO 80202 800-267-2642 Davis, John
1245 S Glencoe Denver, CO 80246-3226 0 Davis, Kay Lynn
5445 DTC Pkwy Greenwood Village, CO 80111 303-486-6888 Davis, Randall Todd

 

[dydavid]

Interesting.

Comment: Pls hide names & addresses above, put in dummy values, so these people won't get junk mail, junk calls, etc.

Dave

 

[suzi3945]

Sorry, Noted..

Bill

 

[KenWright]

Unless your data had a superfluous row in it, or a row missing, then there is no reason it should happen. Go examine the raw data around that name and make sure that there isn't a block of 5 or a block of 3 or something like that. If there is then you will need to add / delete a row. The data needs to be in blocks of 4 for the first formula, or 5 for the second, including any blank rows.

Regards
Ken...............

 

[suzi3945]

Ken:

thanks I fixed it. what happened was there were a few rows that weren't four down.. some were three and some were 3.. I ended up running it and then inspecting the data, finding where it was wrong and then fixing it by adding a row and importing again.

Another quick question.

My name field has the email address of the person imbedded in it with the mailto:fname tage..

Is there anyway to interpret that and not only bring in the fname but also the results of the email tag ?? i.e. email address.


Bill