I am creating a report which needs to ID only new employees. Therefore people who have never been one the list before needs to be IDed. Is there a formula to ID only values (people) that appear the first time based on date criteria on adjacent column?
Thanks
Use this formula
=COUNTIF($A$2:$A$12,A2)=1
that will return "TRUE" if there is only 1 instance and indicate new person.
If you find the suggestion or solution helpful, please consider adding reputation to the post.
You need to sort by ascending date first.
Then use this formula in cell D2 and drag down;
=IF(COUNTIF($A$2:$A2,A2)=1,TRUE,"")
You need countifs, with 2 condition: Name in column A and Date in column B is oldest:
=IF(COUNTIFS($A$2:$A$12,A2,$B$2:$B$12,"<="&B2)=1,"TRUE","")
Quang PT
If you don't want to sort first, you can try this formula in D2 and copy down
=IF(MIN(IF($A$2:$A$12=$A2,$B$2:$B$12))=$B2,TRUE,"")
Note: you need to enter this formula with a Ctrl+Shift+Enter (CSE)
Thanks cubangt but this formula does not address what I am looking for which is to ID the first occurrences.
So question then, you say you want to identify only new employees that have never been on the list.. so how does the date help? If they have never been on the list, then they would only have 1 record on the list regardless of the date correct?Originally Posted by JyothiGrace
Now if you are wanting to find an employee that has been on the list say 3 times and you want the most recent, then you are correct, the formula i provided wont do without some additional logic.
Just trying to better understand the request.
Cell D2 formula , drag down
OR array formula , drag downHTML Code:
HTML Code:
Using SUMPRODUCT = IF(SUMPRODUCT(($A$2:$A$12=A2)*($B$2:$B$12<=B2))=1,"TRUE","")
Thanks...worked!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks