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?

Answers


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)


Need Your Help

Circular canvas corners clickable in Chrome

javascript html5-canvas css3 clickable

I have two canvases. I have made them circular using border-radius. The 2nd is positioned inside the first one (using absolute position).

Detect Windows Mobile vs. “plain” Windows CE

c# .net windows-mobile compact-framework windows-ce

How can my Compact Framework application detect whether it is running under Windows Mobile (Start Menu at top, Menu bar at bottom) as opposed to "plain" Windows CE (Start Menu at bottom, Menu bar i...