Deposit summary report vs. bank statements

[shlomo08701]

Hi,

We're trying to create the following project. I would appreciate getting some input on how to program it correctly.

As a healthcare billing company, we post all account receivables to the patient's ledger. At the end of the month, we need to tie out the deposits posted to the bank statements to confirm that every bank deposit record was posted.

Our software has a "deposit summary" report, which lists all deposits posted to the ledger during a time period.

We want to automate comparing the deposit summary report vs. the bank statements, noting any discrepancies with the amounts, and highlighting which amounts that hit the bank is missing from the deposit summary report.

Please note we can have several of the same amounts within the same month ($100 a few times a month).

Please see the attached sample of the report for one day (1/4/21). I also attached a snip of the bank statement. The total deposit number (in column P), you'll find (hopefully) that amount in the bank statement, although it might not have the same posting date.

We are trying to see if there are any items on the bank statements in January that are MISSING on this report. If they are missing, that means we didn't post this transaction to our ledgers.

We are also looking for the opposite if any transactions on this report are missing on the bank statements; in that case, the money hit the bank in another month.

Thank you

[#2]

@shlomo08701 Not sure if I fully understood, but you could create a pivot table on the basis of the Deposit Summary (see Sheet1). No complicated formulae needed! Use that pivot table to match the total amount per Deposit# to the DEPOSIT_DATE_ANALYSIS sheet (as demonstrated in column E of that sheet), using a simple VLOOKUP function.

[#3]

I’m looking for a formula to match the deposit summary report to the bank statements to highlight which deposits in the bank statements are MISSING in the deposit summary report. I would prefer not to use pivot tables at all.

[#4]

@shlomo08701 Your choice, though I don't understand your preference not to use pivot tables.