Credit card reconciliatrion

[Maheshp]

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.

[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

[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.

[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.

[Maheshp]

Thanks Saveesh for your prompt response.

[Maheshp]

Hi Saveesh,

Need one more help, On below screen shot you can see "[Null]" in all the column. But when I am doing sum up in last column " Diff(SAP-Merchant-Amex)" its showing [Null], ideally it need to shown the difference.

So how can I remove this [Null] in all the columns.

[Maheshp]

Thanku saveesh!

Got the Solution.