Pairing Deposits with corresponding Combined Deposit on Bank Statement

[Pendeco]

Thank you for viewing.... any assistance is very much appreciated.

I have been creating an excel sheet to reconcile bank statements with account ledgers. Someone much smarter than myself provided a near perfect formula to do so, with one exception.

There is one Merchant Service provider who will take 2 - 3 days worth of transactions and deposit in account in one transaction. These deposits can be isolated in both the ledger and on the bank statement.

Totaling the ledger Merchant deposits against the bank Merchant deposits of course does provide me with the correct trial balance... However, particular clients can be very particular.... and would like to see which Merchant Deposits on ledger are outstanding.

I am hoping to do this

Column A= ledger deposits

Column B= Bank deposits

or

Just one column of values as in the rest of sheet (Bank rows 10-250 & ledger rows 255-505)


Ideally, I would like to conditionally format to highlight cleared transactions in there respective cells. However, if there is a status column next to each indicating cleared/out as utilized for all other transactions, this is absolutely acceptable. Again ANY help is much appreciated.

These were the formulas which worked for reconciling exact amounts (

Bank: rows 10 - 250

Column A - Description (Ck #, Date, Merchant, or none)

B - Amount

C - Identifier (differentiates between multiple same-value transactions)

=IF(B10<0,-B10&"-"&COUNTIF(B$10:B250,B10),B10&"-"&COUNTIF(B$10:B10,B10))

D - Status (indicating if matching corresponding identifiers = 2)

=IF(COUNTIF($C$10:$C$1000,C11)=2,"CLEAR","OUT")


Ledger Rows 255 - 505 **** all amounts multiplied by -1 (Amt * negative 1)

A - Description

B - Amount * -1

C - Identifier
=IF(B256<0,-B256&"-"&COUNTIF(B$10:B256,B256),B256&"-"&COUNTIF(B$10:B256,B256))

D - Status
=IF(COUNTIF($C$10:$C$1000,C256)=2,"CLEAR","OUT")

Thank you all in advance

-Pendeco

[jme1013]

Pendeco, I think you've given too much detail and used too much verbiage that makes perfect sense to you but not the reader. You'll probably have more luck if you can reword it and make the request simple and generic.

[AliGW]

Actually, you'll get more success attaching a workbook!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Pendeco]

Example attached

[FlameRetired]

Hi Pendeco. Welcome to the forum.

Will there ever be more than two ledger deposits that equal a bank deposit, and will the multiple ledger deposits always be contiguous as in the example?

[Pendeco]

Dave,

Thank you for the welcome. The most ever deposits combined will be three... typically a Friday - Sunday. This does not happen with much frequency... maybe once a month per location. Most often I see two. Call it 80% of the time combined deposits account for contiguous ledger deposits. A few clients have several locations under the same LLC which share one operating account. In these cases, they are rarely if ever contiguous, as each location will provide revenue data each day, however CC company will only combine deposits from a single location.

These are typically the 80 - 120 pg or more bank statements with a few hundred or more of these AMX transactions.... And the ones which if possible, this worksheet would be most beneficial in time saved and errors averted.

Thanks again

[FlameRetired]

Thank you Pendeco.

Then I would say that without something in common that relates the transactions like acct numbers I don't see this doable by formula ... (please don't post real acct. numbers ... make fake ones ... if they will tie these together)

Sorry.