Moving Rows To Columns in A Table

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

I've downloaded data for a site that's in tabular form but the months are
going across the top with each year in a separate row. I don't think I can
graph that easily. I definitely can't manipulate the data as I could with
everything running in two columns; one for date and one for data.

Is there a function to do this? If not, does anyone know a way to automate
it? I have several like this and they're in different forms.
 
Hi John!

Select the range then Copy it.
Select a cell where you want it to go.
Then Paste Special>Transpose

Biff
 
You have received two suggestions on how to make a 50 x 12 range into a 12 x
50 range, but it sounded like you wanted a 50 x 12 range into a 600 x 2
range

if so, you might try creating a pivot table. with year as the row field, no
column field and each month as a data field. It would come out like this:

Year Data Total
1995 Sum of Jan 493
Sum of Feb 369
Sum of Mar 836
Sum of Apr 342
Sum of May 262
Sum of Jun 673
Sum of Jul 921
Sum of Aug 982
Sum of Sep 235
Sum of Oct 190
Sum of Nov 794
Sum of Dec 475
1996 Sum of Jan 239
Sum of Feb 392
Sum of Mar 501
Sum of Apr 97
Sum of May 835
Sum of Jun 506
Sum of Jul 142
Sum of Aug 792
Sum of Sep 348
Sum of Oct 724
Sum of Nov 271
Sum of Dec 645

Now select the pivot table and do Edit=>Copy, then Edit=>Paste Special,
Values to remove the pivot table and make the fields editable. Select the
second column and use Edit=>Replace What:="Sum of " (no quotes - just to
demonstrate the space on the end), With:="" (leave empty). Then select
column 1 (starting with 1995 in the example) and do Edit=>Goto => Special
and select Blank Cells.

in the formula bar, put in =A2 (where A2 contains 1995), then do
Ctlr+Enter. This will fill in the year. However, it uses formulas, so now
select column 1 and do Edit=>Copy, then Edit=>Paste special and select
values.

You can then create a column to the right that combines Year and Month to
form a date, and another column to reproduce the data. Again, use
Edit=>Copy, then Edit=>Paste Special and select Values to replace the
formulas with just data.

Then you can delete the pivot table area. .
 
You probably need a macro if you are going to do this a lot.

As for a manual fix, assuming you have the year in column A, data for Jan in
B, for Feb in C, etc, first replace text month names with the numbers 1-12.
Then you want to insert a blank column to the left of each data column. Select
just the rows containing your data, without the header row, then Edit/Goto,
click on Special, and select Blanks. B2 must be the active cell, and type the
formula =DATE($A1,COLUMN()/2,1) and press CTRL+ENTER.

Then you'll want to convert those formulas to values. Select *all* of the data
and Edit/Copy. Then Edit/Paste Special and select the Values option. Then
delete the original column A.

Now you can just select the data in columns C and D and paste at the bottom;
then select E and F, etc.
 
Nice one, Tom!!! Sure beats my suggestion of inserting columns and formulas,
and copying and pasting!
 
Tom Ogilvy said:
You have received two suggestions on how to make a 50 x 12 range into a 12 x
50 range, but it sounded like you wanted a 50 x 12 range into a 600 x 2
range ....
Click to expand...

While it may be possible to use pivot tables to do this, it'd require a
separate pivot table for each range you need to convert. That could be lots
of unnecessary work.

A purely formula approach may be better.

If your data ranges contained month labels across the top row and years down
the left column, so 10 years of data would span an 11-row by 13-column range
with the top-left cell empty, try this.

1. In a different range, say, X101, enter the address of the original data
range you want to convert.

2. Enter this formula in X102.

=INDEX(INDIRECT($X$101),1,2+MOD(ROW()-ROW($X$102),12))&"-"
&INDEX(INDIRECT($X$101),2+INT((ROW()-ROW($X$102))/12),1)

3. Enter this formula in Y102.

=INDEX(INDIRECT($X$101),2+INT((ROW()-ROW($Y$102))/12),
2+MOD(ROW()-ROW($Y$102),12))

4. Fill X102:Y102 down into, say, X103:Y401 (sufficient for 25 years of
monthly data). Both formulas will evaluate as #REF! when you've exhausted
the data in the range the address of which you entered in X101.

5. Copy the nonerror portion of X102:Y401 and paste special as values into
the range you want the 2-column data to appear.

6. For the next such original data range, just repeat steps #1 and #5
(assuming you have calculation set to automatic; otherwise, press [F9]
between steps #1 and #5).
 
Back
Top