Excel 2007 : Formula to find discrepancies

[paperwings25]

Alright. I got a stock file uploaded... Stock.xlsx

Instead of going through the 55,000 rows in this excel file, I would love to crunch a formula that would lookup the policy number and make sure that the SUM of the End balance of 2009 along with the transactions throughout the year equals the End balance of 2010.

What I have done thus far is go through the policy numbers, insert 2 lines after the group of policy numbers, manually SUM them, and if they didn't match what the End Balance in 2010 was, I highlighted them. There are some policies that just have a beginning and an end and if they don't match, I highlight them. Any highlighted portion, another department is going to investigate them.

For example, A2:A29 is policy 10020000. A2 is the beginning balance and the Sum of transactions in between and beginning balance equals what the 2010 End Balance has on its books, so we're good on that one. The next one I have highlighted does not match.

Is there any such formula that will prevent me from doing this tedious work? If you need me to submit more information so that you can follow along, then please say so. Thank you!!

[NBVC]

You can make the sample excel file smaller by removing a lot of irrelevant or extraneous information, we only need a representative sample that clearly shows what you need done.

[paperwings25]

You can make the sample excel file smaller by removing a lot of irrelevant or extraneous information, we only need a representative sample that clearly shows what you need done.

Alright. I got a stock file uploaded after deleting irrelevant information.

[paperwings25]

Was there any luck?

[Miraun]

Hey Paperwings;

Thanks for posting the example workbook. Do you have a copy of the raw data, prior to your manual intervention of adding in the additional lines?

What I would recommend doing to do a quasi-quick analysis of the data would be as follows:

Apply the auto-filter onto the sheet.
Do a lookup on Column D for 2010 End Bal
CUT all of the entries for the 2010 End Balance, and paste them to a separate Worksheet.
Remove the autofilter... Now it should all just be the beginning 2009 balance and the adjustments.
Sort the data, just to make sure that all o the UNFI_Policy_No are together... but I assume they will be based upon how the report was generated.
Run the Subtotal function. In 2007, it's under the data tab. You want to run Subtotals on each change in UNFI_Policy_No, adding a SUM function to the JRNL_TRAN_AMT
From here, go back to your ending balance sheet, and use the following Vlookup:
=VLOOKUP(A1&" Total",Sheet1!A2:D62,2,FALSE)

That will put the totals from the subtotals right next to the ending balance values. From there you can use conditional formatting to highlight any values where the Vlookup'ed value is different than the original, pulled value.


Sorry that it's not an automated VBA solution for you... but it's an alternative way that should be much faster for your needs of a quick reconciliation report. It should be pretty easy to follow.

[Miraun]

Alternative... based upon your recent forum comment on sumifs.

1) Run an advanced filter for unique UNFI_POLICY_NOs and move them to another sheet.
2) Equation: =2*Sumifs(B:B,A:A,A2,D:D,"2010 End Bal")-Sumif(A:A,A2,B:B)
Where A:A is the raw data UNFI_Policy_No Column
Where B:B is the Jrnl_Tran_Amt Column
Where A2 is the Unique Unfi_policy number

Values of 0 are balanced.

[paperwings25]

Alternative... based upon your recent forum comment on sumifs.

1) Run an advanced filter for unique UNFI_POLICY_NOs and move them to another sheet.
2) Equation: =2*Sumifs(B:B,A:A,A2,D:D,"2010 End Bal")-Sumif(A:A,A2,B:B)
Where A:A is the raw data UNFI_Policy_No Column
Where B:B is the Jrnl_Tran_Amt Column
Where A2 is the Unique Unfi_policy number

Values of 0 are balanced.

I think this might work. I'm going to test it out on my real file and get back to you. It worked on my stock one. Thank you!

[paperwings25]

Thanks a lot Miraun. I used the alternative solution and I added the conditional formatting into it and got all the values that are different highlighted. This is wonderful. My boss is going to be thrilled. If it isn't exactly what she is looking for, it is so close that it would just need a little tweaking. Thanks!!