Expert: Aidan Heritage - 4/21/2008

Question
I am a graduate student working for a professor here at SUNY Albany and he has given me a task of inputting a very large amount of population data from one Excel sheet to another, and I am hoping to create a macro to do it. (This is data for 45 nations over 55 years, so it would be a huge job to do by simply clicking back and forth and cutting and pasting.)

Essentially what I am doing is transposing data from a column into rows on another sheet. The issue is this - the data is for populations of various nations across time, and it includes the population for both males and females. So, the file looks like this:

Row 1: [column 1] Country Code ... [column 2] Year X ... [column three] Male population

Row 2: [column 1] Country Code ... [column 2] Year X ... [column three] Female population

Row 3: [column 1] Country Code ... [column 2] Year X+1 ... [column three] Male population

Row 4: [column 1] Country Code ... [column 2] Year X+1 ... [column three] Female population

The file I am transposing the data to, however, is set up with just one row per country, so I need the population figures to be transposed.

Row 1: [column 1] Country code ... [column 2] Year X ... [column 3] Male population ... [column 4] Female population

Row 2: [column 1] Country code ... [column 2] Year X+1 ... [column 3] Male population ... [column 4] Female population

So, the spacing isn't the same in the new file as it is in the old one. So, the source data takes up two rows, but the output is only one row, meaning that the macro is a little complicated (advancing two rows for the source each time, but only one row for the output each time). I already have the country codes and years in the destination file, and all I need to add is the population data.

Can you offer assistance as to how to make a macro where the input and output change in this way?

Answer
I SUSPECT that you would PROBABLY be able to do this with formulas (actually, I'm certain you can - the offset function would do it) but the macro would basically need to follow this route

dim looper
dim counter
dim FinalRow
dim SourceSht
'set the source sheet to the correct workbook
'finalRow should be set to the final row you need to read
for looper = 1 to FinalRow step 2
activesheet.range("A1").offset(counter,0).value=sourcesht.range("a1").offset(looper-1,0).value
'similar for each entry EXCEPT that for the FEMALE population you wouldn't deduct one from the looper

next


I've written this very quickly with a number of elements assumed (eg setting the worksheet) but hopefully it gives you what you need - let me know if I can help further though!

NOTE that I've assumed that the data is in the same order - if not, I would suggest using this process to build a consistently laid out data table and then use VLOOKUP to populate the master table from the consistent data table thus created.