Credit card reconciliatrion

Hi,

Need your assistance, we are trying to match the customer credit card reconciliation with merchant report and bank credit. We are facing the below issues while preparing the reconciliation. we have attached the data so if anyone can help us with the necessary workflow.

1. While matching SAP VS Merchant output is giving the matched detail and ignoring the unmatched transactions in the same formula. We want the detail " Matched and unmatched both".

2. In case if there is no unique ID then how do we match the transactions.

3. After matching the transaction how do we create the date wise table. Example Below.

Data.xlsx

Workflow

Accepted answers

All comments

saveeshkumar

Hi @Maheshp ,

What is key column for both sheets?

Thanks

Maheshp

In SAP tab "Reference Key 3" and in Merchant report tab "Authorization Code".

For Example :- in SAP tab "reference key 3" there is one key number MC0000T67838211216, so we are removing last six numbers and bold one number will match with Authorization code"

saveeshkumar

Hi @Maheshp ,

1. While matching SAP VS Merchant output is giving the matched detail and ignoring the unmatched transactions in the same formula. We want the detail " Matched and unmatched both".

Using Join tool we can get matched and unmatched record ,Union tool can merge all records together.

2. In case if there is no unique ID then how do we match the transactions.

we need some reference key in order to match transactions

3. After matching the transaction how do we create the date wise table. Example Below.

Summarize Tool

Thanks

NikyN

Hello there,

I think that you need to specify the pattern for each for every credit card type (VISA, AMEX etc.). As I could not find in SAP any AMEX type -- I mentioned in formula only MC and Visa to get the Auth. code out of the Reference Code.

Let me know if it is usable for you

Best luck!

Niky

Data.xlsx

Credit card reconciliatrion.yxmd

Maheshp

Thanks Saveesh for your prompt response.

1. While matching SAP VS Merchant output is giving the matched detail and ignoring the unmatched transactions in the same formula. We want the detail " Matched and unmatched both".

Using Join tool we can get matched and unmatched record ,Union tool can merge all records together. @ After applying Join tool, system is separating the data of match and unmatched but we want the original data in one file which can shown both match and unmatched transactions. which is presently not happening. Union tool is not working as expected in this workflow, i am also attaching the workflow along with this if you can please assist us on the same.

2. In case if there is no unique ID then how do we match the transactions.

we need some reference key in order to match transactions

3. After matching the transaction how do we create the date wise table. Example Below.

Summarize Tool @ In summarize tool we can only do the sum of the values which is presently not helpful for us we want data wise segregation of the values, i am also attaching the workflow as well as the screen shot for reference.

Sumarize data.xlsx

SGN recon workflow.yxmd

Data.xlsx

saveeshkumar

Hi @Maheshp ,

Please check this workflow i have added the union tool and summarize tool in the existing workflow.

hope this will help you and let me know if need more information.

SGN recon workflow.yxmd

Maheshp

Thanks Saveesh for your prompt response.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703