Macro for turning "titles" into values of new columns (transposing and repeating values)

Hi everyone,
I would badly need a macro to go from what you see on the "what_I_have" sheet to the "what_I_need" sheet in the attached file.
Basically, in this badly formatted file from which I need to start from, there are three "titles" for each column and I need to move two of these "titles" to the values of two new columns I need to create, so that in the end there is just one type of values in each column.

This is the beginning of the table:

		Year	year_1	year_1
		Month	January	January
		MeasuresLevel	Variable1	Variable2
type_1	continent_1	country_1	0	0
type_1	continent_2	country_2	0	0
type_2	continent_1	country_3	2	2
type_2	continent_2	country_4	0	0
type_3	continent_1	country_5	20	74
type_3	continent_2	country_6	22	123

As you can see "Year" and "Month" are like titles and I need to convert them to row values like this:

Year	Month	Type	Continent	Country	Variable1	Variable2
year_1	January	type_1	continent_1	country_1	0	0
year_1	January	type_1	continent_2	country_2	0	0
year_1	January	type_2	continent_1	country_3	2	2
year_1	January	type_2	continent_2	country_4	0	0
year_1	January	type_3	continent_1	country_5	20	74
year_1	January	type_3	continent_2	country_6	22	123

Moreover, the starting file contains many years (and is therefore much longer than the example file I attach, which contains only 1 year).

Could anyone come up with a macro that does this?
I would be extremely grateful, doing this by hand is impossible because I have a lot of files and a lot of years of data.
Thanks a lot!



example.xlsx

Hi Wilco,

Are any of your cells "merged cells". If they are this problem gets a lot harder. Can you attach a workbook sample instead of a picture? To attach a workbook, click on "Go Advanced" and then on the Paper Clip Icon above the advanced message area. I want to write code to do this type of problem and your example would help.

Hi Wilco84,

I'm working on this problem still. I'm trying to make it work for any number of fixed columns to the left of the data and any number of fixed rows above the data. I should be done by tomorrow (or later tonight if I get lucky).

Hey Wilco,

This problem has been on my list for a while now. I've written other code to turn Crosstab Tables in to Excel tables but never with your type of Row Headings. This is the first run on this guy and should work for your specific data. To generalize this problem is much harder than my other code. I think you know why.

I ask 2 questions to try to generalize this code to work with many different arrangements of Crosstab Tables with different number of fixed rows or columns.

1. How many Columns of data are fixed. This is the number of Columns on the left, that need to be repeated.
2. How many Rows of data (at the top) are fixed. This is the number of Rows, at the top, that need to be repeated.

I didn't do this exactly like you had, as you had two columns of values. I did this macro to have only one value column. Pivot tables and normal tables will work with this better.

Give this a try and let me know how to improve it. Click the Star below this post to give me rep if it works as I always look at those replies first.