I need help creating invoices from a Very Jane CSV order report. I would just use mail merge, and format it, but the problem is that the person and their address is one one line, then each product they ordered is one the next line, and then another person, and so on. Word just treats each line as a person.

Example: (I can't show private data)


The real CSV is formatted exactly the same way. And yes, some of the fields don't have names because the customer didn't enter theirs.



All I want to do is make an invoice for each one with what they ordered, their address/email, and my address.

Hi iansan,

Before that data can be used for a mailmerge, the data for each record must all be on one line. At present, you have two (or more) lines per record. Where there are more than two lines per record, you need to have one line per item, with all of the customer information repeated on that line.

As for having one invoice per person, you can do that using a Directory/Catalog merge, but there is no way Word can tell you which customer an item should be invoiced to if the details aren't in the data.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

OK, so to do this I would have to take each product and copy and paste it on to the same line as the customer, with new columns for each product. Not easy for 116 products, but it could be done.

Maybe there is a better way than mail merge?

Thanks

Hi iansan,

You could use a macro to re-arrange the data for a mailmerge. Alternatively, you could use an Excel macro to open a Word document and populate it with the data. Whichever way you go, though, you need a reliable method of identifying which records relate to a given client. I doubt that can be done where the client details are missing - unless there's another field in the data to identify the client by an ID code. Then there's the issue of whether the data are sorted by client. A mailmerge can handle this last issue, but you'd have to program it yourself if you were to automate Word from Excel.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Could you give me a more detailed explanation? I have never done macros before. If not, that's ok.

Thanks, Ian

Hi Ian,
Basically, a macro would have to be coded to look at each row to see whether it has a value in your 7th column. If so (let's assume row 1), it would then look at the next row (row 2) to see if that has a value in your 6th column and, if so, cut columns 2-6 from that row and paste them into, say, columns 8-12 on the current row (1). The code would then look at the next row down (3) to see if that has a value in your 6th column. If so, columns 1-7 from row 1 would have to be copied to row 2 and columns 2-6 from row 3 cut & pasted to columns 8-12 on row 2. Row 2 would then become the 'current' row and the process would repeat until another row is found with a value in your 7th column. At that point the whole process would start over. After all this processing to get the data onto a single row per client & category is done, any remaining rows would be deleted.

As you can see, this is quite an involved process.

If you're going to drive the letter generation from Excel, the macro would also need to sort the data by client, start a Word session, export the data for the first client to it, save a copy, then process the next client untill all are done.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]