Reshape wide data with multiple categories to tall in excel
I have a dataset in excel that Looks like this:
Date Country1-GDP Country2-GDP Country1-Unemp Country2-Unemp
and I would like to produce a normalized dataset like this:
Date1 Country1_Name Country1-GDP Country1-Unemp Date1 Country2_Name Country2_GDP Country2_Unemp Date2 Country1_Name Country1-GDP Country1-Unemp
I would like to do this in VBA so that I can call it as a macro directly in excel after I pull my data in ..
any thoughts or pointers?
First be sure to break you problem into smaller problems:
Find the sheet layout
Identify the number of countries (so you know where the different data ranges are)
Country 1 as basis for the rest
Collect the country 1 columns and add the Country name column for it (use .xlEnd commands to apply it to the right amount of cells, or .OFFSET compared to the date column).
We need to have the country name for this. I guess the country name is in the column titles (followed by -GDP, -UnEmp, etc, so you can easily just get it from the -GDP column, by something like =LEFT(Range.Value2;LEN(Range.Value2)-4), which takes the last 4 charancters off. Keep this as a local variable.
Keep a copy of the Date range (column A:A) for reuse
We will store a copy of the range of Dates as we will use that for every other country to paste under the dates in column A:A
Loop through the remaining countries, using the layout in place from country 1
Now we start to Loop through all the countries 2 - N. Paste the stored copy of dates under column A:A (so 1 set for 1 country), Get the coutnry name (just as for country 1) and put that next to the freshly pasted dates. And country by country pick the different Country characteristics for that country and move the data under the corresponding Country 1 columns.
Sort your table
Sort the data in any order you want (Date / Country in your case)