November 5, 2021
My customer sends me 100 - 200 orders a day using an EDI tool, but also email me a "Template" which lists around 2000 of his most recent orders (some of which we already delivered), and I have to send it back to him with status and product information about each order. The status code is contained in a huge 20,000 row table that I refresh regularly from the server. Easy with a lookup function. Then I use a lookup from a third table to translate the status code into plain engliish the customer can read, and finally a 4th table to lookup some comments which apply to specific item references. Historically I've done this with VBA & lindex match (or in later versions with xlookups), but it is not reliable because the data is often a bit messy. I tried creating a data model but came up with error messages when I tried to establish connections, I think because the data is messy - and anyway I'm not sure Power Pivot and data models is the way to go because I dont want output as a Pivot Table, I just need to bring all the data back to the Template table and done.
All the "help" I can find seems to be about appending data all of similar format (which this isnt) , or merging to create Pivot Tables.
Am I missing something really obvious ?