Unique values per date


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
Who is Participating?
mark_harris231Connect With a Mentor Commented:
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
Saqib Husain, SyedEngineerCommented:
You can try a pivot table approach. I used a helper column in the data to get this. See attached.
ablove3Author Commented:
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.
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?
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.

All Courses

From novice to tech pro — start learning today.