Transposing statement for mailmerge

[Gav13]

Afternoon all,

Bit of an odd one, and I'm probably over complicating the issue here.

Essentially I have a set of data like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust No[/TD]
[TD]Name[/TD]
[TD]Invoice[/TD]
[TD]Date[/TD]
[TD]Due Amount[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]ABC[/TD]
[TD]00001[/TD]
[TD]19/03[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]ABC[/TD]
[TD]00002[/TD]
[TD]20/03[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]CDE[/TD]
[TD]00004[/TD]
[TD]19/03[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]DEF[/TD]
[TD]00007[/TD]
[TD]11/02[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]DEF[/TD]
[TD]00009[/TD]
[TD]01/03[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]

That, for the purpose of a mailmerge I'd like the Invoice, Date and Due Amounts to be listed horizontally instead so

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust No[/TD]
[TD]Name[/TD]
[TD]Invoice 1[/TD]
[TD]Invoice 2[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Amount Due 1[/TD]
[TD]Amount Due 2[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]ABC[/TD]
[TD]00001[/TD]
[TD]00002[/TD]
[TD]19/03[/TD]
[TD]20/03[/TD]
[TD]4000[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]CDE[/TD]
[TD]00004[/TD]
[TD][/TD]
[TD]19/03[/TD]
[TD][/TD]
[TD]3000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]DEF[/TD]
[TD]00007[/TD]
[TD]00009[/TD]
[TD]11/02[/TD]
[TD]01/03[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]

No real preference on the field order, so it could for example be Invoice 1, Date 1, Amount Due 1, Invoice 2, Date 2, Amount Due 2 etc. instead.

This is for thousands of rows of data so any help is greatly appreciated.

Cheers!

 

[Gerald Higgins]

Will there only ever be a maximum of 2 invoices per customer number / name ?

 

[Gav13]

Hi Gerald,
No there can be a lot more unfortunately. No way to confirm for sure the number.
If this is an issue however, it would be possible to, for example, extract before this exercise any statement with more than 10 invoices, so limit it to that.

 

[Macropod]

There really isn't any way for a mailmerge to restructure the output as described. Although the field coding in my Microsoft Word Catalogue/Directory Mailmerge Tutorial (http://www.gmayor.com/Zips/Catalogue Mailmerge.zip) could be adapted to produce a linear output, that'd look rather strange once the data start line-wrapping.

 

[Gerald Higgins]

Personally I would do the transposition within Excel, and then use the results to input into your mailmerge.

But with a number of invoices that is very variable, that transposition might be tricky.