Show Latest Information For Employees

  • Hi ,
    I have an excel file with 2 tabs. tab 1: Active and Tab2: tenure.
    Tabe 1 has the following column headers
    Manager
    Employee Name
    Dept
    Assignment
    Original Start Date
    End Date
    Bill Rate


    The Employee names may be repeating in this tab becasue whenever the employee is extended for a new assignment he has a new start date and new end date and new assignemtn and rates....This helps track the historical information about the employees.


    TAB2: (tenure) also has the following column headers
    Manager
    Employee Name
    Dept
    Assignment
    Original Start Date
    End Date
    Bill Rate


    In this tab , i want a macro to look for each name just once and extract the latest information - Oldest start date, Newest assignment, newest rate, and assignment etc.


    I am attachign the file for your reference.


    Please help me figure out this problem.


    thanks
    Fatima

  • Re: Show Latest Information For Employees


  • Re: Show Latest Information For Employees


    Thanks Jindon,
    The code does work and does brings back the names (once - which are reprating) with thier dates and assignements. But it does no tbrings back the most recent assigbment and hte most oldest dates ( start dtes). I have made a table for the results it should bring. If you could please have a look at it ...i owuld really greatly appreciate that..


    thanks

  • Re: Show Latest Information For Employees


    Change

    Code
    1. Else
    2. x = b(.item(a(i,2)), 7)
    3. b(.item(a(i,2)), 7) = IIf(a(i,7) > x, a(i,7), x)
    4. End If


    To

    Code
    1. Else
    2. [B]x = .item(a(i,2))
    3. If a(i,7) > b(x, 7) Then
    4. b(x, 6) = a(i,6)
    5. b(x, 7) = a(i,7)
    6. End If[/B]
    7. End If