Hi. First let me say, I'm a total newbie when it comes to VBA/Macros, I don't even know how to enter them into the worksheet/workbook.
I am also assuming that this will require a VBA/Macro, if there is an easier way, fantastic!
I have several hundred entries for one inventory item of when it was received and sold between 2010 and 2017.
I have 2 versions of the report, the original on Sheet 1, and the "post condense" on Sheet 2, and I need to find the discrepancy, it is off by 1. I can't do a line by line comparison because the condense grouped some items into one inventory adjustment. So I am trying to sum by month.
This is the formula I came up with:
For Jan, 2010... SUMIF($A$2:$A$325,">=01/01/2010",$E$2:$E$325)-SUMIF($A$2:$A$325,">=02/01/2010",$E$2:$E$325)
For Feb, 2010... SUMIF($A$2:$A$325,">=02/01/2010",$E$2:$E$325)-SUMIF($A$2:$A$325,">=03/01/2010",$E$2:$E$325)
Etc, etc, you get the idea...
I apply the formula to both versions of the report, then I compare the outcomes to each other IF(G2=Sheet2!G2,"ok","!"). Once it compares and finds the offending month, I can compare the few transactions and find the differences.
It works quite nicely, but manually changing the column/row info and dates takes forever. I need to use this same formula for over 300 different items that have discrepancies. I can copy and past the 81 versions of this formula (11 per year, 7 years 5 months) that I have already created, but I will inevitably have to change at least the end of range at some point. Find and replace would be usable there, but still, it's time consuming.
I would like one thing that I can plug in that will look at column A for the Date and Column E for the Quantity and return totals for each month/year in column G, regardless of the date range (whether it starts in 2010 or 2012) and regardless of how many rows there are... if there's a date in column A, it does the math on column E. Then I would like to have it compare the results of the 2 sheets to each other.
I hope I explained it well enough. I have attached my messy excel sheet in case anyone needs a look.
Anyway, all help is greatly appreciated!
Bookmarks