I am using Word 2021. I know from searching that people say Word still can't do a "many to one" mailmerge, but if you use "NEXTIF{}" and variables to compare things with the same record number, is this possible with the merge fields or even VBA? My limitations are I need to use a .CSV file (converting to something else would just add a step), and I have no control over the format of the data as given to me. For example, only the first line contains the repeated data for things on the same order (Name,date,etc.). The ManyToOne plugin is not an option partly for this same reason.
My merge fields and script save a variable for "order ID" to compare the last one to the current one and enter them in the table on the invoice if they are the same and skip to the next record if they are not. My problem is that after the last item on one invoice, the NEXTIF{} has caused it to go to the next record, so it skips the one it was on. I lose the first item (record) on the next invoice. I need to either be able to have a PREVIOUS record function or do a test on the next record without advancing to it.
Here is the data format:
And here is the embedded merge field code. The second row repeats for the number of rows I have room for on the page. I am printing out the MERGESEQ, MERGEREC, ID1, and ID2 for debugging.
From my research so far, there doesn't seem to be a way to do this without a "cheat" because you need to be able to compare one record to another. Once you do a "NEXTIF" to get that field data, you are on the next record and can't go back.
My "cheat" based on a youtube video here https://www.youtube.com/watch?v=MIfid43hlHw, was to open the CSV in Excel and create a macro that inserts a blank row, goes to the last column + 1 and adds a heading called "compare", goes back to the first column, copies that data (order_id) and pastes it offset up by one into the new column. It then deletes the inserted row (along with the first record in the pasted column) and saves it back out.
What this does is enter the next record's order_id into the current record's data for a compare in the merge document.
Here are the first 2 rows in the table that processes the data. To allow for however many items you need on an invoice/packing slip, you cut and paste the second row into each remaining row. My data includes a shipping total and the total for the order in the first record (sold item) for each order_id, so I save them for later.
The first record just prints normally. The first column in the next row saves the "model_number" (current item) then goes to the next record with NEXTIF if the order_id is the same as the compare field (the order_id of the next record". If this test is false, we are done and it goes to the next invoice.
If it does go to the next record, I then set the ID2 to the model_number of this next record. Because there can't be more than one item with the same model number on an invoice (if they ordered more, it just adds to the quantity), this is the easiest comparison to check and suppress the output of each row's test if there is no item to print. Without this, the first item would repeat however many times in each row if there was only one item with that order_id.
If the items are different (ID1 <> ID2) it knows this is a new item with the same order_id and prints it to each cell in the row.