Matching / Reconciliation Problem (Credit Card)

[Champagne Killy]

Hi Excel experts,

I could really do with some help here as my company is looking to cut overheads at the moment and I don't want to be part of the cull.

I'm trying to match a credit card receipts spreadsheet (from our bank) to my company's internal record of customer payments.

The problems are:

1)The company spreadsheet breaks the payment up into several constituent parts each on it's own row. The bank spreadsheet just records the sum total on one row.
2) One credit card could be used to make more than one payment on the same day or on a different day.
3) As there may be more than one customer with the same last 4 digits on the credit card, the match process needs to make sure it only uses one customer order number per match (this only appears on the company spreadsheet)

Potential Matching identifiers
1) Bank Spreadsheet - Transaction Date (column E), Last 4 digits of credit card (col F), Amount (column I)
2) Company Spreadsheet - Transaction Date (col A), Customer Order number (col B), Last 4 digits of credit card (col H), Amount (col I)



On both spreadsheet I would like to be able to identify those rows that match and those that haven't been matched.

Any help woyld be greatly appreciated... come on guys, I know you can do it?
Cheers
Killy

 

[pbornemeier]

Please post 20 or so lines from the bank worksheet and enough of the company worksheet to match the bank input, with a few rows from non-matching transactions. (see Excel Jeanie link in my sig).

Does the bank transaction data ALWAYS match the company transaction date (not always in my experience)? If not, what is the max difference you have seen?