• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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