• Status: Solved
• Priority: Medium
• Security: Public
• Views: 544

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
• 2
1 Solution

Commented:
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

Author 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

Commented:
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.

Featured Post

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