Re: Sumproduct to calculate differences between two lists
I thought it would turn out to be more complicated than your original question suggested.
Personally, I would be finding out exactly what is required before I started developing anything, as the requirements may well determine the approach.
However, albeit without having seen your data or what you want to develop, that would probably not be my approach. I suspect I would create a single table from the 12 monthly employee lists. Against each row I would manually add (in separate columns) a year number (in case the analysis eventually spans years) and period number. I would then concatenate the two into a single year/period string, in the format yyyy-mm, in another column.
Depending on the reporting requirements, I would add separate columns to identify (Y or N/blank) whether the person is a joiner or leaver in that particular month. I would then add columns to calcuate whether they have moved out of, or into, the department in the month. Similarly, create any weekly/monthly employee type data.
Once all the data is in place, I would build a pivot table on the results. You could create a rolling 12 month report by adding on the next month's data, filling down the formulas, etc., and using the pivot table's filters to display just the data you want to see.
If you opt for a VBA solution, it is likely to be far less flexible than this sort of approach, where you can add extra columns of data whenever you want, and also respond rapidly when users decide they want something slightly different.
I hope this is of some use.