Forum Discussion

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

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

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.