Each month, my client sends me an Excel file of their updated widget list. The client's file is 26 columns in total. My file is close to 90 columns. The first column of both files carries a Unique ID# [UID].
The UID is supposed to be the "anchor" or whatever it's called that allows the files to be compared and merged. A co-worker has used V LOOKUP to perform this task but it's not working correctly. After the merge, the data on my file doesn't perfectly match the most recent customer-supplied data [discovered on spot-check].
Each record in their file has a UID and I have the same UID for existing records on my document.
Widgets occasionally change names but UIDs are forever. When a widget retires, so does its UID.
When a new widget comes along, so does a new UID.
The order of the first 20 columns of the client file matches the order of the first 20 columns on my version of the file. On my file, the next 60+ columns are a combination of customer-supplied [and updated] data and [mostly] data that I track and manage for them. The order of these columns do not match.
Each row represents one widget on their file. My file sometimes uses several rows for the same widget. Where I use several rows for a single widget, only the first row carries the UID.
When data is present, I need the customer's file to overwrite what's on my file for the corresponding cel/column. Widget names, manufacturer, PO#, etc.
How do I compare the data in each file and then import the customer-supplied changes to my file without losing the data I've been updating and managing since the last update?
Thank you, in advance, for your expertise and generous assistance!