I have two databases with client information that I need to merge. There's about 20,000 records so it's way too much to do manually.
Sheet 1 has the client records and their unique identifying number, along with the date of their order. Clients may appear more than once.
Sheet 2 has additional client information such as age and associated department.
I've created helper columns on both sheets that concatenate the ID and date to create a string: ex ID: 12345678, date number is 42466, so helper column is 1234567842466
Generally, index match works well for this, matching based on the helper columns and pulling in the info I need. However, some records have incorrectly differing dates ex: sheet 1 has recorded a client on 4/6/16, but sheet two says that same client was actually on 4/4/16, even though the dates should match. It's a bit of a mess, but as far as my data integrity goes, it's acceptable.
What I would like to do is find some way to do an index match or similar with the exact ID, and the exact or closest date. Is this possible?
Thanks!
Bookmarks