Introducing rows in between every observation for huge panel data sets.

[pjroots]

This may be hard to explain, but here it goes.
I need to fix up some data I have for some econometric analysis. I
have average years of schooling for every country in the world from
1960-2000, staggered every 5 years (i.e. 1960, 1965, 1970, etc. 9
observations total). So I need to introduce 4 empty rows in between
every observation for each of the 130+ countries in my dataset so I
can have continuous years from 1960-2000 (40 observations for each
country). Then I will have to interpolate for the empty cells by
taking averages (for example, if I have 1960 = 1 and 1965= 1.209, take
(1.209 - 1)/5 = 0.0418, then replace 1961 observation with 1+0.0418 =
1.0418, then 1962 (1.0418+ 0.0418) and so on.
Let me know if there is a macro way of doing these "somewhat simple
operations".

My data looks like this:

country year tyr15 tyrf15 tyrm15
Afghanistan 1960 1 0.385 2.363
Afghanistan 1965 1.209 etc.
.. 1970
..
..
Algeria 1960
..
Zimbabwe 1960

tyr15: average years of schooling for population age 15+
tryf15: '' '' females age
15+
trym15 '' '' male age 15+

I would have to interpolate for all three of these variables.
Any kind of help would be extremely welcome. I'll be merging the
corrected data into STATA after I'm done. If anyone knows how to do
this in STATA, please let me know if it is easier (eg. running a
loop).
I have no background in programming, so I don't even know how to run
the actual macro. So any instructions of how to do this operation
would be very helpful.

Thanks a lot.
P.S. If anyone wants to see the data, I could send you the excel file
so you can take a look at it.

 

[Bob Phillips]

Put this code in a standard code module and run it

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 3 Step -1
.Rows(i).Resize(3).Insert
Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I'm going to take your "...I don't even know how to run the actual macro..."
literally and add some info to what Bob put up.

To get Bob's code into a standard module: Open the workbook. Press
[Alt]+[F11] to open the VB Editor. From that menu choose Insert | Module.
Copy and paste the code Bob provided into the code module and close the VB
Editor. To run the macro use the Excel Menu: Tools | Macro | Macros and
then choose the ProcessData entry in the list and click the [Run] button.