Forum Discussion

BetsyD's avatar
BetsyD
Copper Contributor
Nov 16, 2021

How to reconcile data with duplicate unique values

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 cod...
BI & Data Analysis
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 17, 2021

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?

 

 

 

 

  • BetsyD's avatar
    BetsyD
    Copper Contributor
    Nov 17, 2021

    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.

Resources