Count Consecutive Days Based on Unique Number
Hello everyone,
I am trying to find a formula which count consecutive days for each unique personal ID number in column A and get the result in column N. For example, Walter Jones has 19 consecutive worked days, Marco Polo has 21 consecutive worked days and so on. Also, data in column A to I will continue to be added as days pass by. Data in column L to M might change depending employees get hired or terminated.
See example for better detail
Thank you everyone for your help and time
Hi Kimston,
See the attached with a helper column formula. I've also needed to sort by Date (old to new) and then my name to make the Helper column work. See if you can live with a few extra steps in the process to get an answer.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Not sure how you get 19 for Smith, I get 20...
=COUNTIF($E$2:$E$78,M2)
What am I missing here?
edit: OK I see what I was missing, needed to scroll left
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
Here is another method that uses a few more helper columns but sorting isn't needed.
Thank you very much Mr. MarvinP and Mr. FDibbins for your help. The only questions that I have are why do we have to use 4 helpers? Also, is it possible to use the PersonID numbers rather than the person names? The reason for this is because there are several employees with the same first and last name.
Again,
Thank you for your time and excellent work!
I have done it with 1 helper.
I2=IFERROR(IF(INDEX(B3:$B$78,MATCH(E2,E3:$E$78,0))-1=B2,1,0),FALSE)
copied down
I added some extra columns while playing with this, so Im not sure what your original columns where, but but this next to where your unique names are...
=SUMIF($E$2:$E$78,S3,$I$2:$I$78)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks