Reformat Data Question

S

sslack

How can I get data that looks like this:

A B C D E

1 YEAR JAN FEB MAR ....

2 1947 21.5 21.5 21.9 ....

3 1948 23.7 23.5 23.4 ....

4 1949 21.5 21.5 21.9 ....

5 ........ ....... ....... ....... ....

Into a format like this:

A B

1 1/1/1947 21.5

2 2/1/1947 21.5

3 3/1/1947 21.9

... .............. ......

13 1/1/1948 23.7

13 2/1/1948 23.5
 
R

Ron Rosenfeld

How can I get data that looks like this:

A B C D E

1 YEAR JAN FEB MAR ....

2 1947 21.5 21.5 21.9 ....

3 1948 23.7 23.5 23.4 ....

4 1949 21.5 21.5 21.9 ....

5 ........ ....... ....... ....... ....

Into a format like this:

A B

1 1/1/1947 21.5

2 2/1/1947 21.5

3 3/1/1947 21.9

.. .............. ......

13 1/1/1948 23.7

13 2/1/1948 23.5
Click to expand...

In some cell, enter 1/1/1947. In the cell below it, enter 2/1/1947. Assume
these cells are H3:H4.

Then select both cells; put the cursor over the lower right corner until it
turns over a cross hair, then copy/drag down and you should see the dates
incrementing by month.

Your DATA starts in A1 (which contains the word YEAR).

In H4: =OFFSET($A$1,MATCH(YEAR(H3),YEAR),MONTH(H3))

Then copy/drag down as far as needed.


--ron
 
D

DOR

Assuming your original data is in Sheet2 and is formatted so that 12
months are shown per row, and the Jan 1947 data is in B2, place the
following formula in Sheet1 cell A1

=DATE(OFFSET(Sheet2!$A$2,INT(ROW()-1)/12,0),ROW(),1)

and in Sheet 1, cell B1 put

=OFFSET(Sheet2!$B$2,INT((ROW()-1)/12),MOD(ROW()-1,12))

Copy down as far as necessary.
 
D

DOR

Sorry, formula for date column should have been

=DATE(OFFSET(Sheet2!$A$2,INT((ROW()-1)/12),0),MOD(ROW()-1,12)+1,1)

This will generate the right year if any year has bveen skipped in
sheet2.
 
Top