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

    match&max (excel2003)

    hi all,

    in the attached file, I need a formula help, that will do the following, match name and no in sheet1 with name and no in sheet2, if match exist, then replace inidate (col d) in sheet1 for the matched name and no with the maximum date from stdate (col g) in sheet2.

    Regards,
    dubdub
    TIA
    dubdub

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

    Re: match&max (excel2003)

    1) A formula in column D will ALWAYS overwrite the existing value. If you want to keep the old value if no match is foumd, you must either use a formula in another column, or use a macro.

    2) The values in column G on sheet2 are text values, not dates. You will have to convert them to dates, for example as follows:
    - Select an empty cell.
    - Press Ctrl+C to copy it.
    - Select column G.
    - Select Edit | Paste Special...
    - Select the Add option.
    - Click OK.
    - Select Format | Cells...
    - Apply a date format.
    - Click OK.

    3) The only match for hafs / 115 has date 13-May-01, not 01-Jul-01, so I cannot reproduce the desired result.

    Here is a possible array formula (confirm with Ctrl+Shift+Enter) for row 2:

    =MAX(IF((sheet2!$A$2:$A$9=A2)*(sheet2!$B$2:$B$9=B2 ),sheet2!$G$2:$G$9))

    Fill down as far as needed and format with the custom format <code>dd-mmm-yy;;</code>

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

    Re: match&max (excel2003)

    many thanks Hans for a detailed answer.

    dubdub
    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
  •