Recently, I was asked whether I could provide some assistance with manipulating data, that had been exported from a CAT results test.  The problem, had slowly been driving a colleague of mine, very slightly crazy!

It had been quite some time since I’d last written an Excel Macro, so in many ways I relished the challenge.  Thus here is the story.

The Problem

  • To take exported data that was presented in a tabular fashion (over 6 columns).
  • To re-present the data, with the last three columns transposed into their own row, with appropriate column title.

It’s probably easier to view than to explain (see below screen shots) – but in short, this was a classic convert columns to rows exercise!

The Solution

Time was not on my side when approaching this, so I took advantage of the fact the there were many ‘known’ set values that wouldn’t change (column titles & column counts etc).  As such I ‘hard-coded’ these into the solution.

A little looping and incrementation proved to be the answer.  Below is the final script used.






There was very little time for any proper code analysis or architecture – a simple, get the job done was the order of the day.

Put simply, it worked and did indeed, get the job done, resulting in a very happy customer.

Link to example spreadsheet.