So, mail merges are easy as hell, we all know this. However, I am doing something slightly different with this particular mail merge, and assembling the data in an efficient manner is proving difficult. Explaining it clearly has also proved slightly difficult, but I will try my best.
I am in the process of creating a settlement document - an invoice of sorts - that has information regarding a large amount of refund cheques that I issued to a large number of vendors. The information includes the vendor's name and address, product, the unit price of the product, and the number of units we refunded money on. I am setting up the mail merge to populate those fields so that I don't have to manually create over a thousand settlement documents.
The problem that I am running into is this - I did not create the spreadsheet, and every single piece of data is on a separate line, in its own cell. That is, lets say there's a vendor who sold 10 items - there are 10 rows, each of which containing (Vendor Name) (Address) (Product) (Unit Price) (# of Units). Attempting to create a mail merge with the information laid out like that would yield me 10 separate invoices for ONE vendor. What I need to do is bring each field's data into ONE cell per field, so that when I run the mail merge, it sees all 10 products as one cell, and populates as such.
I played with the ideas of perhaps using a pivot table, or a vlookup (which I don't fully understand how to use) or creating a table in Word and then pasting into Excel (which literally did nothing) - so far, the only thing I could come up with was copying the data from the spreadsheet, pasting it into Word, then copying the groups of information into the FORMULA bar of the desired cell, which yielded the desired effect (pasting directly into the cell automatically puts each line of information into its own cell). My only issue here is that it is incredibly time-consuming. I feel like I must be missing something, some way to consolidate the information much more quickly and efficiently.
This is a long-winded query, I know, and I apologize, but any help you could offer would be greatly appreciated!
Thanks!
Bookmarks