+ Reply to Thread
Results 1 to 3 of 3

Formula to find highest payscale & earliest date in highest payscale group

  1. #1
    Registered User
    Join Date
    09-19-2008
    Location
    Netherlands
    Posts
    1

    Formula to find highest payscale & earliest date in highest payscale group

    Hi,

    Which formula do I have to use to find the highest payscale group of a person and the earliest entry date of his current payscale group
    Here's my data. Wanted values marked with X, I want to add an indicator in column D, to make filtering possible. As a result I need the earliest payscale entry date

    Pers.no. Pay scale Start date
    00100003 89 01/09/2000
    00100003 90 01/01/2001 X
    00100003 90 01/02/2002
    00100003 90 01/10/2003
    00100004 90 01/09/2001
    00100004 90 01/10/2001
    00100004 91 01/01/2002 X
    00100004 91 01/01/2003
    00100005 91 01/07/2005
    00100005 91 01/01/2006
    00100005 92 01/07/2006 X
    00100005 92 01/01/2007
    00100005 92 01/01/2008
    00100008 88 01/01/2003 X
    00100008 88 01/01/2008
    00100008 88 01/07/2008
    00100009 88 01/06/2008 X
    00100010 88 01/07/2000
    00100010 88 01/09/2000
    00100010 88 01/01/2001
    00100010 89 01/07/2007
    00100010 90 01/01/2008 X
    00100010 90 01/07/2008

    Thanks in advance

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try this in D2:
    Please Login or Register  to view this content.
    the formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around it.

    Then copy it down the column

    Adjust ranges to suit... and reconfirm with CSE keys.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Formula to find highest payscale & earliest date in highest payscale group

    With
    your sample data in A1:C24

    Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead
    of just ENTER):
    Please Login or Register  to view this content.
    Copy D2 and paste into D3 and down as far as you need.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date Cycle Formula (For those who need it)
    By vamosj in forum Excel General
    Replies: 0
    Last Post: 04-16-2007, 02:25 PM
  2. Pick the earliest date from a selection of dates...
    By -emma- in forum Excel General
    Replies: 9
    Last Post: 01-24-2007, 08:16 PM
  3. Urgent HELP for Formula for a date problem on Excel!
    By flip_360_2002 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2006, 12:14 PM
  4. Assignment overDUE PLEASE PLEASE HELP!!
    By undergrad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2006, 12:34 AM
  5. Replies: 5
    Last Post: 11-10-2006, 06:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1