• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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