[mamcelr]

Help,

I've seen several examples of Compare & Calculate, however I have a twist that has stumped me. I have two separate files based on an "as of data date" that I want to compare and then calculate the delta between two numbers and leave the result in the current working file.

The twist is that the current years working file may not have all the rows that are in the previous years file and may also have new ones. The rows represent accounts. An account that is paid to zero during the current year will not be in the current years report. A new account will appear in the the current years file, but not the previous years file.

I need a result that shows the change in the account balance even if the current year doesn't have a row representing an account paid to zero. So the result shows all of the accounts that appear in both reports as though both reports had been consolidated and then shows the change in value of the account balance, either positive or negative, as compared to the previous year.

I've attached a file that uses worksheets (named 2010, 2011 and Delta) as an example. Note accounts #2, #4 and #5 paid to zero and do not appear in "2011", yet appear in "Delta" with a negative number representing the reduction in balance between "2010" and "2011".

Thanks for any help you may be able to provide . . .

[Keebellah]

Hi, welcome to the forum.
Can you be more specific?
I see the two sheets 2010 and 2011 and the Delta.
You will have to add a comparisson that will first check if the account occurs in both sheets, but what you want it to show?

Do you want to see these accounts of 2011? If so then just the balance, so no calculation if the account is only present in 2011.
Since you have not included your macro I cannot tell you what to change, but prior to your comparsion you will have to veridy something like if account is presnt in 2010 and 2011 then preform differnec calculation els just show value of 2011 in Delta

I hope this gives you a hint

[mamcelr]

Keebellah,

Thank you for your reply.

To be more specific, I'm simply trying to calculate the difference in account balance. The result (Delta) needs to include the account number, the name and the delta between 2011 and 2010, or 2011 balance (minus) 2010 balance.

Here's what's stumping me. All accounts in both years need to be calculated, but the way the data is presented, some accounts may appear in one year but not the other. For instance, a new account would not be in the prior year (2010) and an account paid to zero would not appear in the current year (2011). Accounts that do not appear in both years have a zero balance in the year that they do not appear.

So, if an account appears in 2010 and not in 2011, the account is assumed to have a zero balance in 2011, i.e. 0 - $400,000 = ($400,000). This would be an account paid to zero. If an account appears in 2011 and not in 2010, the account is assumed to have a zero balance in 2010, i.e $400,000 - 0 = $400,000. This would be a new account. Again, the calculation is always 2011 minus 2010, but all accounts regardless of whether or not they appear in both years, need to calculated.

Does that help?

[Keebellah]

Basically what the macro needs to do is if one of the accounts is not present in one or the other year, just show the value of that account.

[Keebellah]

Try attached sheet.
I wrote a macro named Delta to (re)build the Delta sheet.

Some explanation I put in the VBA code

To run the macro you can press Ctrl+Shift+D (assigned shortcut) or just choose the Macro Daelta and run it.

Let me know if it works.

The Delta values for 2010 which are not in 2011 are negative, if you want to change this then all you have to is remove the * - 1 in the line of code in the VBA modlue

The * I used to indicate the rows that only exist in the previous year.

The code is quite dynamic, yopu only have to add a new sheet for the new year and the code detects it, so you will not have to rewrite anything for each year.

Let me know if this is what you needed

[mamcelr]

Hans,

Perfect! Your work is very much appreciated, and WAY over my skill level in Excel. It works great in the sample file, my challenge will be to convert it to the working database. I may have questions. Thanks again.

Mac

[Keebellah]

Just ask

[mamcelr]

Hans,

I imported data from my two working files into the spreadsheet and at first it seemed to work beautifully. To make sure, I asked an associate to go into the file and manually check 40 random accounts. Four of them had incorrect calcualtions. I discovered that those four accounts had other account numbers with the exact same name associated with it, which happens quite frequently.

I asked them to go back in again and check 10 more accounts that had multiple accounts under the same name and all ten were incorrect. I can't release the file with the actual names but I did flag those that were incorrect and gave names not only to the incorrect account, but also to the secondary and in some cases the tertiary account related by name.

I can't tell if it's something simple like the sort process at the end of the macro or something else more complex. Can you help again?

Regards,

Mac

See attached file

[Keebellah]

I'check it this weekend, no problem.

[Keebellah]

First of course the sorting problem and second, my code expected names in column C

I changed the macro code to check on the column B contents and I also filled your 'manual' check on the fly to so can see what is written where

Let me know. Have a nice weekend

[mamcelr]

Hans,

I've been out for several days; my apologies for the delayed response. As usual you've proven what I'm trying to do is possible and that my Excel skills are much lower than the "intermediate" level I have indicated in my profile. Thank you again, the corrected macro worked perfectly!

If you are willing I'd like to take it to the next level and incorporate it directly into the files I actually use. If you scan back to my original post, I compare two separate files. I use a file that has an "end of prior year" data date and the second file is one that is identical in structure, but produced for the "current month". I've been copying and pasting the data into one file as an example test bed to run your macro. If I could somehow adjust the macro to look at the "end of prior year" file and compare it to the "current month" file and leave the result in the "current month" file that would be the best of all worlds.

Of course as we move into 2012, my end of year file would change from 2010 to 2011 and I would be comparing each month in 2012 to end of year 2011. So the macro would need to allow me to change that file name.

Is this something you think is possible and is it something you might be able to help me with? I understand if it is too much to ask. You've been extremely helpful already.

Thank you,

Mac

[Keebellah]

I will gladly hep you but this will have to wait until after Nov 24 since I'll be out of the country for my eldest daughter's marriage.
I'll post you a PM when I return and we can look into this.

[mamcelr]

Thank you and congratulations! My daughter just got married two years ago . . . it was a great time for family and friends to gather and celebrate. Enjoy . . .

[Keebellah]

Hi, I'm back, what's the status?
Still need assistance?

[mamcelr]

Yes, I still need help. Thank you for reaching out. Hope you had a great time while away!

I have attached the actual files that I'd like to compare using your macro. Every month the attached file is created by simply inserting pre-formatted data into the tab named "DATA" and saving the new file with the current months name. Column "T" contains the value I would like to compare, in this case column "T" of the current month (10-31-2011) compared to column "T" of the prior year-end (12-31-2010).

I have inserted a tab in the 10-31-2011 file named "Delta vs YE 2010" where I'd like to place the results of the comparison. It also contains the columns of information I'm interested in viewing.

As I mentioned above, each month, we copy the new data into last months file and just rename it. So each month I'd like to compare the new data to the same prior year-end file. Then in January of every year, I'd start comparing the new year's monthly data to the new prior year-end report. Given that, I'd need some way to to insert the new file name into the macro every month for the current data and once a year to insert the name of the new prior year-end file.

Hope this all makes sense. Let me know if you have questions and thanks again for all your help!

Regards,

Mac