Restructuring data with a macro?

[JAMatthews]

Hello,

I'm trying to transpose/restructure/normalise some monthly survey data that is delivered to me in a very unhelpful way (I have no control over this).

I want to get from this:

Organisation	Sector	Region	No	Age	%	Region	No	Age	%	Region	No	Age	%
Organisation 1	Manufacturing	East	10,000	29	30	North West	5,000	29	30	North East
Organisation 2	Retail	East	15,000	53	10	North West	600	29	30	North East	10,000	29	69
Organisation 3	Oil & minerals	East	200	38	22	North West				North East	1,300	29	56

To this:

Organisation	Sector	Region	No	Age	%
Organisation 1	Manufacturing	East	10,000	29	30
Organisation 1	Manufacturing	North West	5,000	29	30
Organisation 1	Manufacturing	North East
Organisation 2	Retail	East	15,000	53	10
Organisation 2	Retail	North West	600	29	30
Organisation 2	Retail	North East	10,000	29	69
Organisation 3	Oil & minerals	East	200	38	22
Organisation 3	Oil & minerals	North West
Organisation 3	Oil & minerals	North East	1,300	29	56

This is just an example of the data structure. The actual dataset contains sensitive data and is too large to attach here. For anyone kind enough to help out, I have attached 'test' spreadsheet containing data in the same structure as above: Example.xls

Can someone point me in the direction of a macro that would be able to transpose several groups of columns into rows, while repeating the columns which contain organisation metrics (e.g. 'Sector' in the example above)? I have basic knowledge of VBA but I suspect this will require a For...Each statement, which is just over my head at the moment.

I have already tried using the pivot table wizard trick suggested in other forum posts but that doesn't seem to do what I'm looking for, unless I've completely missed something. I've also tried using relative references but I find that approach very clunky and difficult to update (again unless there's an easy method for doing this). The only tool I've seen that does something of this sort is SPSS, but I'd like to stick with Excel, if possible.

[patel45]

Create a new sheet named NEW then run this macro

Please Login or Register  to view this content.