Merging / matching two lists according to multiple criteria with possible duplicates in

Hi,
I have two sets of what should be the same data, taken from different sources. These include variables such as
Purchase Order Number,
Order Date,
Order Amount,
Customer's email
and others.

We're trying to match the two lists to find errors (those would be the unmatchable rows).

The thing is, the values (each one by itself) aren't unique in a data set. That is, the Purchase Order Number can appear in multiple rows in Set A and in Set B, for example. Other details would be different, such as Order Amount.
The date is tricky and could be 1-2 off between the two lists for the same purchase order.

Anyway, what I'm trying to ask here is how do I match these lists according to the multiple criteria, given that some values can be repeated? If everything was unique I guess I could find my way with VLOOKUPs.



Thanks for your help

Hi,

I have the same issue with a problem of reconciling my inventories between what I say I have and what my vendor says I have in their warehouse.

What I have done is created a vba solution to match the lists both ways based on the unique ID for each line (in my case, it is a combination of PO number and Item number)

in order to get the unique ID of the combination of both, I created a helper column inserted to the left by concatenating the columns which make up the unique ID


say you have the PO number in B1 and vendor number in C1 and that makes the unique ID, then insert a column at the beginning and in column A type...

Please Login or Register  to view this content.

drag formula down, do the same thing in the other sheet, and now you can use vlookup...

if you can't insert the columns as the beginning, you can also add to the end and use a combination of the index/match functions.

Let me know if this helps, if not post a sample and someone can look at it in more detail.

thanks.

Thanks for the answer. My case is a bit more complicate since the same fields in the two datasets can vary slightly. For example, the sale date can be 1 day off since the two sets are from different time zones. Also, there could be up to a $1 difference in some sales figures (decimals issue), so I needed a bit of flexibility. Plus, the natural key was not unique.

What I ended up doing was kind of a left join in access, where each row in set A were joined with all possible rows from set B. This of course led to multiplications. Then I took it to excel and added columns to calculate whether the dates matched within 1 days difference (in abs. value) and $1 difference (in abs.). I deemed the rows that got a true value for both criteria as the correct matches.

It wasn't pretty, but it got the job done.
(correctly, I hope )