Wage History showing raise amount, date it occurs.

[aggie81]

I can download payroll check history for several years into a workbook showing
Employee ID
Employee Name
Check number
Check Amount
Date
I want to create a spreadsheet showing current pay rate, Date of last raise, Raise amount and previous pay rate for each employee.
I thought it would be easy to do but there are several pays with the rate and it has me stumped on how to even begin.
I hesitate to post a file because some people are touchy on how much they make and you never know what can come back on you from the Internet.
Any help is appreciated.
Thanks,
Lee

 

[Hui]

Lee

Can you post a sample file and an example of the output you require?

 

[aggie81]

The Wage History worksheet has the format for what I am looking for from the Wage Data worksheet.
Thanks,
Lee

 

[NARAYANK991]

Hi ,

See the attached file.

The formulae are quite lengthy , and may impact your worksheet recalculation time if your data is voluminous.

Narayan

 

[aggie81]

Thank you it is working very well and what I needed.
Thank you very much Narayank991!
Lee

 

[p45cal]

In the attached, which includes Narayank991's solution for comparison, is a solution that might not be so resource-hungry, involves only one simple formula, and copes with the possibility of an EmpID's pay changing, then changing back to a previous value some time later.
First the Wage Data sheet needs to be sorted thus:


Then a formula in H2:
=G2<>G1
copied down.
and a header in H1 (I used 'change').

Then create a pivot table (do I hear a groan?) like this:

and in the pivot table, filter the 'change' field to show only TRUE,
sort the Period Start Date Newest to Oldest.
That's it.


To demonstrate the correct showing of a change back to a previous pay rate, I changed EmpID 39's pay rates in cells G19:G22 (highlighted) from 22.5 to 22. To see the effect of this you need to Refresh the pivot table (already done in the attached).


I realise the data is not in the same horizontal format you specified - sorry.

 

[aggie81]

Thanks p45cal that works great too. I will need to remember to do the sort but it is a simple and fast way to display the information.
It will depend on the end user as to how the information is displayed as Pivot or linear format.
Thanks to both for a great help.
Lee

 

[p45cal]

I will need to remember to do the sort

This sort of thing can be automated with a macro.

It will depend on the end user as to how the information is displayed as Pivot or linear format.

Likewise a macro could convert the pivot into the format you want.

Say if you want that.

As an aside, a tweak to the simple formula to make the result more robust: make that formula in H2 this:
=OR(G2<>G1,A2<>A1)

It just guards against when the last PayRate at Check of an Emp ID is the same as the first PayRate at Check of the next EmpID, which the first formula would have missed.