How to reconcile data with duplicate unique values

[BetsyD]

I need to reconcile large amounts of transactions created in POS system to the corresponding transactions processed thru the merchant account. The POS system often creates duplicate authorization codes,  the auth code is the unique value I use to reconcile the transactions in the merchant account. I do not want to delete the duplicates because they are part of the revenue being reconciled and need to be accounted for. At present I do a Conditional formatting - Highlight Duplicate Values in both sheets (POS & merchant) and manually amend duplicated auth codes to create a unique value - then I do a vlookup between the sheets. I have attached an example of "raw" data here. Auth Code 103267 appears twice in POS & merchant but one transaction is for $5 and the second is for $10. vlookup will only pull from the first instance in POS and that's why a differentiation needs to be made between the duplicated auth codes. Auth 837480 only appears once in POS but is duplicated in merchant data. vlookup treats it as a match when it isn't.

I'm wondering if there's perhaps something in Power Query that could deal with this type of issue.

[#2]

@BetsyD If you mean to say that the total amount for an authorisation code in POS needs to be equal to the total for that code in the Merchant data, you can indeed use PQ.

Group both tables by Authorisation code, Sum Amount. Then you can compare (i.e. merge) these two grouped tables and identify discrepancies. So, 102367 will be for $15 in both, which is OK, but 837480 will be $5 in POS and $10 in the Merchant table, which is not OK. Correct?

[#3]

Hello @Riny_van_Eekelen 

Thank you for the reply. That may just work. I could identify auth codes with discrepancies and then I could just modify the ones where the totals don't match. I've been using a reconciliation method started by someone else and haven't taken the time to look back at the process to see if there's an easier way. Thanks for the perspective.