Want to use Excel for account reconciliation

I am responsible for account reconciliation for 15 businesses. Each business has 4 accounts (let's call them checking, savings, money market, and credit). The businesses transfer funds among each other to their matching accounts (so, a business transfers $200 from their checking to another checking, never from checking to savings).

I get a print out every month of all the transfers, both in and out. Right now I manually go and check to make sure they all match up. Of course they don't, and I have to mark the discrepancies.

I'm hoping to use Excel for this, but a) don't know how to input the data (so I can properly classify that it's out or in from this business, from this account, and this amount) and b) what I would do after that. Any help would be appreciated!

Sorry, I wasn't completely sure how to even set up the workbook; that's why I didn't post one. But, here's an example to help clarify.

It's in the workbook, but here's my notes:

• The $500 does not match (Cheetah has it transferred in from Rhino, but Rhino only transferred out $499.50) -- this would need marked
• The $22.50 does not match (Cheetah has it transferred in from Rhino, but Rhino never transferred it out) -- this would need marked
• The $12.50 matches (Cheetah has it transferred out to Rhino, Rhino has it transferred in from Cheetah) -- no discrepancy here

Account reconciliation example.xlsx

Originally Posted by royUK

Can you get the data in csv format? If you can open in Excel & use Conditional Formatting to highlight discrepancies

I have to manually input the data myself (for now, although working on getting it electronically). My first problem is that I don't know how to format the data to be able to do this. I have uploaded an example attachment to post #4.

I can do conditional formatting for each cell, but that would result in 1800 formulas (unless I'm doing it wrong). 15 businesses times 4 accounts times 2 (in and out) times another 15 businesses (potential transfers).

The other problem with this is that most of the values are discrepancies -- meaning the whole workbook would be red. Could I then somehow pull those numbers to put in a report?