[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

Formula Assistance - Excel 2010

I have a table in Excel that includes the following 4 columns:  Employee number, name, date and Job Title.    I download all employee information into the table after each pay.  I would like to have a column in the table that would allow me to identify the most current job title information  (i.e. a unique list of the most current job title by Emp #).  Is there a formula that I could incorporate into the table that would place a 1 next to the latest information that would allow me to use an Auto Filter and obtain a unique list of all the employee's most recent titles?  I hope I've explained this clearly.  Thank you for your assistance.  

Michael
0
Michael V Bernot
Asked:
Michael V Bernot
  • 2
1 Solution
 
redmondbCommented:
Hi, mvbernot.

Please see attached. The formula in E2 is...
=IF(C2=MAX(IF(B2=$B$2:$B$20,$C$2:$C$20,"")),"1","")

Please note that this formula is array-entered - you must use Ctrl-Shift-Enter to enter the formula - Excel will then put curly brackets around the formula.

Regards,
Brian.Latest-Job.xlsm
0
 
Michael V BernotAuthor Commented:
Thank you for your time Brian!  This is exactly what I needed.  I'm having a little difficulty in understanding what is happening inside the IF statement, but I think with "Evaluate Formula" help I might be able to see how this works.   Thanks again!
0
 
redmondbCommented:
Thanks, mvbernot.

The innermost IF is the heart of things. It compares this row's Name with every other rows' Name. Anywhere there is a match, the matching row's Date is returned, otherwise "".

The effect of this, using row 3 as an example, is to return an array of Dates (in Serial No. form) and ""...
{"";40969;40909;"";40940;"";"";"";"";"";"";"";"";"";"";"";"";"";""}
...from which the MAX functiom gives us 40969.

The final step is the outermost IF comparing this row's Date to the value from MAX.

I keep an eye on "my" closed questions for at least a couple of weeks so please feel free to post any queries/issues here.

Regards,
Brian.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now