Formula Assistance - Excel 2010

Posted on 2012-08-23
Last Modified: 2012-08-24
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.  

Question by:Michael V Bernot
    LVL 26

    Accepted Solution

    Hi, mvbernot.

    Please see attached. The formula in E2 is...

    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.

    LVL 5

    Author Closing Comment

    by:Michael V Bernot
    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!
    LVL 26

    Expert Comment

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


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now