But today I have the opposite issue. My data has been given to me in a horizontal structure (like the second code example), and I really want to force it to be vertical. Basically, I received two records, one each for John and Mike. What I really want is six records, and it would look like this:

It seems like this would involve creating two new fields. One field ("Category") would essentially be an array of values {A; B; C} that would force each record to split into three. The other field ("Value") would just be a calculated field that pointed to the values in the A, B, or C fields depending on what was contained in the Category field.

I could do this in Excel rather simply, but I would love it if I didn't have to. Any ideas?

Monarch lets you do this - from about v6 and above - using the MultiColumnRegion.

In the Report window, select the first line of detail (John). Don 't create a template yet, but in the Templates menu, set the MCR to be active, with 3 columns, starting in position 6 with a width of 3. Back in Template > New Template, define your detail template and make sure the tickbox for MCR is active. You only need to define two fields - "John" and "5". Now define an Append template with MCR active so that A B and C are picked up.

You should have the data you want in the Table window. MCR is amazingly powerful and let you handle all sorts of tricky problems. Give me a shout if you'd like any help with it.

Olly, this is intriguing. Thank you for your help and for pointing MCRs out. I had never really used them before, but I suspect I will definitely be playing around with them in the future.

I may have oversimplified the data that I'm dealing with though. Here is a censored version below. Is it possible to have multiple columns of varying widths? Or irregularly spaced? The data that I'm working with doesn't have any headers, but I'm going to add them in just to point out what I'd like to trap. And if it simplifies the process, I think I can edit the original data source to provide headers for use in an append template (if necessary).

I only want information from records that start with AA00000X, so the 9- records are irrelevant. This data represents the effect of a proposed rate change on a list of policies. The Od# series is the old premium, and the Nw# series is the new premium. Unfortunately there is extraneous information in between these series, and the field widths are not uniform.

What I would like to eventually turn this into is data that looks like this:

By using a MCR like you suggested (with a column width of 14), I can grab the first five columns of the Od# series. But beginning at Od6 I start to run into problems. The field width for the Od# series prior to Od6 is five, but it needs to switch to three thereafter. Moreover, the column width needs to switch from 14 to 10. This would grab the next four of the Od# series, and then I would need to reposition again for the final two. (I am also interested in the Nw# series. But I believe/hope that, once the Od# series is successfully catpured, the Nw# series will fall into line without much trouble.)

Yes, you can define columns that can cope with variable widths, and it helps that in your case it looks like all the columns in each row are populated.

The trick is to define the MCR with the right number of columns (24? in your case, bearing in mind Monarch's limit of 40), each with a column width of 1, and with the left hand position of the first column way over in the right hand margin of your report (e.g. position 300 or somesuch). Then define your data as one big field, which I usually call , and define a calculated field :

A Multicolumn region doesn't actually need to include anything within it from the report. If I defined a MCR with 5 columns at the right margin of my report, I would effectively be multiplying each record by five? Each group of five records would be indexed by a column number from the MCR, and I could do various operations on whatever detail information I do pull in based on which column() value the record has?

There's a lot more to it than just odd-width columns - for example, you might want to connect data with date ranges to data with specific dates. So table A has got entries that indicate a condition applied on a range of dates like:

01.01.2012 - 29.02.2012

and table B has got that something that says that an event happened on a particular date:

15.02.2012.

There's no way a conventional external lookup from B to A will get you a join in this sort of scenario - you need to blow up table A so that you have one record for every date in the range, and this technique, iterated twice, lets you do that.