• 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.

Highlight new joinee..Using Match, index, Indirect, Row & Countif

Asheesh

Asheesh

Excel Ninja
Hi Guys..

Please see the attached...I am looking to highlight the new joinees compared to previous month only..I have already written the formula see column D(highlighted in yellow)...but it is giant..

Is there a better way to deal with it other than VBA and PT..

Appreciate your help in advance...
 

Attachments

Minor correction in the formula - ie in the attached

IFERROR(INDEX(INDIRECT("C"&MATCH(EOMONTH(B2,-2)+1,$B$2:$B$31,0)+1&":"&"C"&ROW()-COUNTIF($B$2:$B2,B2)),MATCH(C2,INDIRECT("C"&MATCH(EOMONTH(B2,-2)+1,$B$2:$B$31,0)+1&":"&"C"&ROW()-COUNTIF($B$2:$B2,B2)),0)),"New Joinee")
 
Hi Asheesh ,

Does the following not give the same results ?

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,"<=" & B2)=1,"New Joinee",C2)

Narayan
 
Hi Sir - I had tried this...but this logic doesnt work...coz there are some rehire cases..who join us back...and these rehires are treated as new joinees for the latest month...so the best way to check is if this employee exists in previous months data set or not..that was the logic i tried to build around..becuase an employee can not quit and rejoin the same month...

Please see the attached, I have highlighted the difference..!!
 

Attachments

Hi Sir -

So this is how the logic works..

1) HC dump is appended to previous months file on 1st of every month..so for "month" Column dates will always be like... 01/01/2014..for next month it will be 02/01/2014...so on and so forth..suppose..I am working on 1st feb data...once done there will be no updates made to it until 03/01/2014...and on 1st march the changes would simply be to append the data..

2) Now in the attached file...assumption is...that employee "a" has left the org sometime in the month of Feb...that is the reason he is not reflecting in the month of Mar(coz data was prepared on 1st Mar)..then he rejoins us on say 30th Mar..and will now reflect in the data set that is appended on 04/01/2014...

So any such instance means that the person is a new joinee...

Hope I have been able to answer your question..?
 
Hi Sir - thank you for your help...

I did spot the error..some emp no's were incorrect...and moreover, you used emp no as base and I am using emp name...I have corrected the emp number in the attached and your formula works perfect...(and yes, it is a good idea to use emp no as a base)

Now, one more question..since both formulae are long...which one would be better to use as my database is very huge i.e. (15000 rows and 49 columns)..You have come up with an array formula and I am using a non array solution..but at the same time my formula contains volatile functions like Indirect & row...

Please suggest..
 

Attachments

Hi Asheesh ,

I suggest you try both in your file and then confirm which one is faster.

However , if we can write down the logic , we can use helper columns which will clarify the logic of the formula , and probably make it faster.

1. We check if the combination of the employee number and the date being less than the dates so far within the column is the first one we have come across in the column ; here , I assume that the dates in the column B ( labelled Month ) will all be the first of the month.

If the combination is Employee number = X , and date = Y , then if we have come across a combination where the Employee number = X , and date <= Y , then there are three possibilities :

a. The employee name is the same

b. The employee name is not the same

If situation a , then we check for whether the same employee number X occurred in the previous month ; if yes , then it is not a New Joinee else it is a rehiree , and hence will be labelled New Joinee.

If situation b , then it is a New Joinee. Or a mistake of the employee number / name.

If the same combination has not occurred , then it is a New Joinee.

Is this logic correct ?

Narayan
 
Hi Asheesh,

Can you check the performance of these function.

Non-Array:
=IF((COUNTIF(C$2:C2,C2)=1)+IFERROR((MONTH(B2)-MONTH(INDIRECT("B"&LOOKUP(99^99,ROW(C$1:C1)/(C$1:C1=C2),ROW(C$1:C1)))))>1,FALSE),"New Joinee",C2)

Array:
=IF((COUNTIF(C$2:C2,C2)=1)+IFERROR((MONTH(B2)-MONTH(INDIRECT("B"&LOOKUP(99^99,ROW(C$1:C1)/(C$1:C1=C2),ROW(C$1:C1)))))>1,FALSE),"New Joinee",C2)

Regards,
 
Thank you guys..

@NARAYANK991

Though all the above formulae work absolutely fine...I would still clarify the logic and is pretty simple

And yes, you are almost right..

1) If an employee rejoins the company he would again be given the same employee number as before..which means we can rely on emp no..& there is no question of duplication..

2) Yes, your assumption about dates is absolutely right..

@Somendra Misra

Smart use of Lookup...I was thinking to use it...but you are ahead of me !! ;)
 
You must log in or register to reply here.
Back
Top