Excel - latest date change

[Jackmetickler]

Hi there I am hoping someone can point me in the right direction.
I am attempting to pick out the latest salary update/reduction from personnel data - for example

Employee Ref Date effective Salary Old Salary
1001 01/04/2011 15000 14000
1001 01/06/2011 16000 15000
1003 01/04/2011 20000 19000
1005 01/04/2011 19000 20000
1006 01/04/2011 15000 14000
1007 01/04/2010 15000 14000

I need the latest salary update.... so for 1001, I just need the update from the 01/06 not the 01/04
I need to see both reduction/increases.
I do not need to see any that have not changed in last 12 months like 1007.
I cannot seem to get all combinations.
Using Excel 2007 but have other versions if easier.

 

[SkipVought]

hi,

I'd use a PivotTable and the Max of Salary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jackmetickler]

Thanks for the advice; not to au fait with Pivot tables so will need to investigate and see if I can suss out.

Thanks
Jack

 

[SkipVought]

I've just looked at the details and here's the approch...

First pivot on
[tt]
Max of Date effective
Employee Ref Total
1001 1/6/2011
1003 1/4/2011
1005 1/4/2011
1006 1/4/2011
1007 1/4/2010
[/tt]
Then add a column to the right of the PT to return the appropriate salary...
[tt]
Max of Date effective
Employee Ref Total Salary
1001 1/6/2011 $16,000
1003 1/4/2011 $20,000
1005 1/4/2011 $19,000
1006 1/4/2011 $15,000
1007 1/4/2010 $15,000

[/tt]
using this formula...
[tt]
=SUMPRODUCT((tSalary[Employee Ref]=F4)*(tSalary[Date effective]=G4)*(tSalary[Salary]))
[/tt]
assuming Excel 2007+ that the salary table is a Structured Table Named tSalary and that the pivot table TOP LEFT CELL is F2.

If you have Excel 97-2003, use named ranges based on your headings. using Insert > Name > Create -- Create names in TOP row...
[tt]
=SUMPRODUCT((Employee_Ref=F4)*(Date_effective=G4)*(Salary))
[/tt]





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}