Unique values per date

Posted on 2012-08-30
Last Modified: 2012-09-04

I need a quick and easy way to determine the an individual's salary change and the date the change was made.

The data below, show the annual salary each salary makes, per pay check.  At some poing the salary changes due to an adjustment promotion, extra.

Employee 1 has 18 pay periods.  I only need to see the check effect date and pay for the starting salary and when the salary changed.

Data return

Employee    Salary eff       Pay
 1                 12-19-11         6,227.01
 1                  3-12-12          6,444.95
Question by:ablove3
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    You can try a pivot table approach. I used a helper column in the data to get this. See attached.

    Author Comment

    I like this method, but I'm looking for something a little easier.  I have to refresh this data quite often and I was hoping for a little quick formula that I hadn't thought of.
    LVL 10

    Expert Comment

    Though maybe not frequent, I would assume that an employee's salary could change multiple times in a given year (e.g., promotion, transfer, demotion, annual review, etc.).  How would you want multiple occurrences to be handled?
    LVL 10

    Accepted Solution

    if you have any flexibility in the layout, you could do the following (which addresses multiple changes per employee):

    1. Arrange columns in order of Employee #, Salary, CK Eff Date
    2. Select Columns A & B
    3. Perform an Advanced Filter and copy unique Employee/Salary pairs to another location (e.g., Cell J1, which will place unique pairs in columns J & K)
    4. Place the following formula in Cell L2: =INDEX(C:C,MATCH(1,(A:A=$J2)*(B:B=$K2),0))
    5. Use Ctrl+Shift+Enter to make the formula an array formula
    6. Drag/Copy the formula down Column L for as many unique Employee/Salaries pairs exist
    7. Format Column L as Date

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now