Sumproduct to calculate differences between two lists

    Hello all,


    I have got a little stuck on a sumproduct query, and know its something basic! Basically I have a Data sheet with information that each month adds in a list of employees. What I want to do is compare the leavers and joiners by department, and the by department is stumping me. If it was a total I could use this:-


    Code
    =SUMPRODUCT(--(ISNUMBER(MATCH(Data!A25655:A27588,Data!A23751:A25653,0))))


    So the two data ranges are for April and March - I could then calculate the number of rows for the range, then subtract the number of employees that happen to be in both current month and previous month (and vice versa to give me both joiners and leavers ).


    The question therefore is how can I add in the cost centre "Prod" to the calculation (ie I wish to know how many employees that are in cost centre "Prod" are in the current month but weren't there previous month (so must be a joiner), and also then do the reverse of seeing how many employees were in "Prod" previous month but now not found in the current month (ie they must be a leaver) ? I could write vba to do this but I am sure a formula could do the same, just need a nudge!


    Thanks in advance

    Re: Sumproduct to calculate differences between two lists


    I apoligize, but I cannot visualize exactly what you are asking. Could you please upload a dummy file with a setup similar to your current file. No need for major details.

    Re: Sumproduct to calculate differences between two lists


    Hi,


    In this example, March's employee numbers are in A3:A12 and their departments in B3:B12. The equivalent April data is in E3:E12 and F3:F12.


    The formula:
    =IF(ISERROR(MATCH(A3&B3,$E$3:$E$12&$F$3:$F$12,0)),"LEAVER","")


    entered in C3 and filled down will calculate the March employees who left. It's an array formula, so needs to be confirmed with Shift + Ctrl + Enter, not just Enter. I'm sure you can work out the joiners calculation.


    However, what happens if someone changes department? Your logic (or at least how I interpret it) will show them as leavers and joiners, when presumably they are not.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Sumproduct to calculate differences between two lists



    Thanks for this. Now I have spoken to them again, it turns out it is a lot more complicated than working out the joiners and leavers simply between two dates. What they actually want is a breakdown month on month for the whole data set, so I will need to vba it anyway ie the cumulative list of joiners and leavers for the previous 12 months, rather than just joiner/leaver between two dates


    I will need to take a list of the unique employee numbers, then work out the min and max month of data set, and the build out a table of whether they are monthly or weekly paid, and also by department. I can then calculate if they are true leavers or whether they are moving department (or even if then become monthly paid rather than weekly). I think this is what they really want rather than a simple calc. The table would then give me an array of 1s and 0s which I can then add up. Sound like the right way Batman?

    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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Sumproduct to calculate differences between two lists


    Thanks Batman, I always like to know how people approach things to broaden my understanding. I went along similar lines - basically I had to write to a column whether they were a leaver, joiner or a leaver joiner if they did both in same month. They didn't care about if they changed from weekly to monthly or different departments, though I did write it in such a way that it could be implanted later (they have a habit of saying defo not in requirements then few weeks later changing their mind!). So to give whether they are a "leaver" or "joiner", in essence I work out the month for the current record, then add 1 for next month, and subtract one month for previous. I then "countif" how many of the months there are to give me a range to look in. I then use an if statement to see if the employee number is found in the range for the following and previous month (Current), previous but not following (leaver), following but not previous (joiner), or neither following nor previous (joiner/leaver).


    Because the ranges are relatively small, the whole module code runs pretty quick (5 secs). The code below was used as a basis (ive merged it into a bigger module now), but if anyone else gets stuck, hope the below approach is of use


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login