I have a spreadsheet that has been presorted by another application. In the current spreadsheet state, it only lists one order per customer. For many customers, we have more than one order during the year which are in a separate spreadsheet. We are wanting to add in the other orders per customer without disturbing the current sort order as it is required for proper distribution of a letter to each customer. We will then be able to draft an order summary list for each customer from the full order list & print them in the presorted order.
So I was thinking about assigning a key field to the current customer list in its sort order and then copying the entire customer/order list into a second worksheet and then comparing the two worksheets via a column in this second worksheet & add the same key per customer. I would then sort this second worksheet by this key, which would result in all records per customer being in the same order as the current spreadsheet.
Here are sample sheet images to illustrate -
Spreadsheet that has no multiple orders per customer; in the sort that needs to stay in:
Spreadsheet that has multiple orders per customer; in basic alphanumeric order by 'company':
As you can see, all company names listed on first sheet are present on second at least once while several have multiple orders per company.
Maybe there is a better way than my general logic. I'm looking for formulas/macros to get this sort of thing accomplished.
Thanks for any help!
John
So I was thinking about assigning a key field to the current customer list in its sort order and then copying the entire customer/order list into a second worksheet and then comparing the two worksheets via a column in this second worksheet & add the same key per customer. I would then sort this second worksheet by this key, which would result in all records per customer being in the same order as the current spreadsheet.
Here are sample sheet images to illustrate -
Spreadsheet that has no multiple orders per customer; in the sort that needs to stay in:
Spreadsheet that has multiple orders per customer; in basic alphanumeric order by 'company':
As you can see, all company names listed on first sheet are present on second at least once while several have multiple orders per company.
Maybe there is a better way than my general logic. I'm looking for formulas/macros to get this sort of thing accomplished.
Thanks for any help!
John