High Volume Bank Rec Issues

Hi all,

I have a client with thousands of transactions in quite a few different accounts that need bank reconciliations every month. This process, for now, consists of someone downloading a .csv file from the "Books" (currently Quickbooks) and downloading a file (with the same date range) from the online banking system of account activity.

So, I have two exports in excel. The fields I'm concerned about are: 1. Date and 2. Amount. That's pretty much it. There is more detail than that, but this is what needs to match up.

Now, I basically take the date and amount from each table, and put them in a new tab. The new tab now has two tables. A "bank-side" table which has dates and amounts (negative and positive for checks and deposits), and a "book-side" which has the same two columns.

My question is, how do I tell Excel, "I want to match amounts of one table to amounts of another table. The match has to be perfect, i.e. to the penny, and the match cannot be contained in the same table, it has to match the other table. For example, if I tell it to highlight duplicates, there can be a case where the "Bank side" has two checks for the same amount, and the "book-side has nothing. In this case, the Conditional Formatting will highlight these two as matches, when, in reality, for the purposes of my exercise, they should not match. They aren't a bank to book match.

The process I'm doing now is flawed because I'm using conditional formatting and it's capturing items in the same column as matches to the same values which I don't want. The match needs to be on the second table, or it's not a match at all.

Hope this makes sense! Please help, I've been doing this the clunky way for a while now, and I need to save some time here, I know there's an easier way.

Also, the second issue is that conditional formatting doesn't set you up with a unique way to identify that a cell is a "Match" or "unmatched" except visually. Example, it can highlight the cells, but if I need to add a column to show "x" is for matched items and "y" is for unmatched items, I would have to visually go through each cell and mark an "x" or a "y" next to them.

Let me know if there's a way to do this formula-based to where I can tell excel, if you find the match (aforementioned) from book to bank, put an X in the next column, same row next to the "Amount" so I can then run sumif's and other summaries.

Thanks for any help, it's much appreciated!!!

-Bradley

Can you please post a small sample file (not image) showing expected outcomes.

To upload a file, click "Go Advanced" then scroll s=down to "Manage Attachments"