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:

Cust No	Name	Invoice	Date	Due Amount
12345	ABC	00001	19/03	4000
12345	ABC	00002	20/03	4500
23456	CDE	00004	19/03	3000
34567	DEF	00007	11/02	2000
34567	DEF	00009	01/03	2000

<tbody>
</tbody>

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

Cust No	Name	Invoice 1	Invoice 2	Date 1	Date 2	Amount Due 1	Amount Due 2
12345	ABC	00001	00002	19/03	20/03	4000	4500
23456	CDE	00004		19/03		3000
34567	DEF	00007	00009	11/02	01/03	2000	2000

<tbody>
</tbody>

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.