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.


SAMPLE SCREEN SHOT BEFORE

Before

SAMPLE SCREEN SHOT AFTER

After

 

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.

  Share Post