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
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.