Transposing Data

[StewartS]

We have a number of worbooks that have the following column lables

Cost Centre Expenditure code April May June etc
AAA 100 10 20 30

A number of times it is necessary to convert it to a format

Cost Centre Expenditure Code Month Value
AAA 100 April 10
AAA 100 May 20 etc

At present I tend to do a copy and paste if there aren't too many but at other times there can be large volumes of data. I am open to pointers for an easy way to put this into a macro. At present I'm just not sure where to start this so would like some tips rather than a complete way of doing it.

Thanks

Stewart

 

[GlennUK]

If you insert a column between Expenditure code and the months, and make a single entry for Cost Centre & Expenditure Code, with a separator to enable splitting them again afterwards ( like =A2&"!"&B2 ), then use that as the first column of data in this method of Normalizing data:

http://www.tek-tips.com/faqs.cfm?fid=5287

 

[StewartS]

Thanks it works a treat

 

[GlennUK]

That's great!