Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formula help (excel2003)

    Hi all,
    What I have in the attached file is two sheets, DB is a plan and OPR is the executed part of the plan. In OPR column L , M & N I wan find from DB the NEXT NAME, STARTDATE AND THE SCHEDULE NAME.
    Reqards,
    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula help (excel2003)

    Please explain clearly in words how one should determine the next name etc.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help (excel2003)

    to illustrated it by example,
    look up for rigid(WHD-12) ,name(xxxx_436) and planned start date(31-jul-03) in sheet drlg-opr into sheet db, and after you find a match, under the same rigid,look up for the next orderly date and extract the name and the schedulename that crosspond to that next date and output these three data into the l,m,n columns in sheet drlg-opr. applying this, the results should be XXXX_462,4-Sep-03, SCHEDUL1 for rigid whd-12 in sheet drlg-opr in column l,m,n is and for rigid whd-14 it will be YYYY_411,8-Dec-06,SCHEDUL 2.

    regards,
    dubdub
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula help (excel2003)

    The array formula (confirm with Ctrl+Shift+Enter)

    =MATCH(A2&"|"&B2,DB!$B$2:$B$32&"|"&DB!$C$2:$C$32,0 )+1

    in O2 will return the index of the row containing the next date. The formula

    =INDEX(DB!$C$2:$C$32,$O2)

    in L2 will look up the next name, and similar in M2 and O2. All these formulas can be filled down.

    See attached version.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help (excel2003)

    many Thanks Hans,

    Can you please clear for me the function of the "I" in the formula.
    TIA
    dubdub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula help (excel2003)

    You want to match on two columns: A and B vs B and C. To match on both columns at once, I concatenate them, with a vertical bar | in between as a separator. In this particular case, the vertical bar isn't really necessary, but it can be useful if you have pairs such as "DA3" and "1FG" on the one hand, and "DA31" and "FG" on the other hand. Without the separator, both pairs would be concatenated to "DA31FG", causing a false match. With the separator you get "DA3|1FG" and "DA31|FG".

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help (excel2003)

    thanks for the clarification.
    TIA
    dubdub

Posting Permissions

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