• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding out exits and new joinees

V

Veeru106

Member
Hi,


I am looking for some solution to my issue here.


I have data in 2 sheets.. Sheet A clearly told me in which Cost center has how many ppl working .


Sheet B show me details of these persons along with their joining dates.


In Cal sheet we have count of ppl in sheet A and sheet B and their variance in col. G


Now if we see we have only 1 variance in G5 but actually we have more of these, because there are scenarios where one person left that particular CC and another joines , hence number remains same.


Issue is how we can find out which person joins and who left.


Please suggest any formula for this or any vba code


Thank you
 

Attachments

  • Veeru2.xlsx
    14.8 KB · Views: 6
Hi ,

The column labelled Person IDs in Sheet A is not having real IDs , since these are single digit and two digit numbers , whereas the column labelled Person IDs in Sheet B has 3-digit and 4-digit numbers.

Narayan
 
Yes that's the issue.....Sheet A doesn't contains person ids...it only contains there numbers....means for CC 4850 we have 15 persons at that point of time...Sheet B contains person ids in detail which is 14, means one person has left or may be 2 left and another new joinee join and we need to find out who are those new joinee and exits..thanks
 
Veeru106, how would you solve it manually? Can you explain the process?
If it can't be done manually, I seriously doubt even the best of formula/VBA wiz can do it. Excel can do magical stuff, but there is no magic in Excel.
 
If we look ar col. P.... Any person who joined after after 16th May will be new joined.
So for each cost center.... We need to look if there any person who joined after 16th May...
So same with exists.... If we have 4 in one Cc in sheet A and 3 in Sheet B and there is no new joinee then means there is one exit
 
Hi again,

Would it be do-able to add an extra column containing the exit date in the raw data? Then you can do the analysis based on the IDs like expressed in #3: we need to find out who are those new joinee and exits.

1. if date of joining >= 6th May then new
2. if date of leaving is between report date and previous report date is would be an exit (rule to be defined, just giving an example).
3. else a stayer.

And you would never need to compare 2 tables where the first one only contains the counter of IDs and not the individual IDs.

Also I noticed the same person ID appears twice in the list for the same CC. What does that mean? The person left in between. On which date?
upload_2018-5-29_16-44-14.png
 
No actually this is the issue….findings exit date is not possible
I think by mistake we have same person id twice...
 
I find that strange in HR, but I understand the master data issue.
Then you'll never know who really exits, apart from mutations. These could be found if the same ID enters a different CC.
Like you've said in "7, follow the lead of Naranyan. I sure do not have a better alternative.
 
You must log in or register to reply here.
Back
Top