Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    All the column (DC) titles are different. I want to search each column and
    return the results to a blank sheet in the workbook - return the column name
    and the most recent date where the result in the S/F field (there are 2) is
    "S".
    The first S/F field tells if something was successful on the first try; if
    it was not (status is F) and there may be a retry and that status is in the
    second S/F field.
    For example, on the attached sheet, LAN last date should be 031609, AUB is
    032109, CST is 030909, and WCH is 032109

    Results will just need the DC (LAN, AUB, CST, WCH) and the most recent date

    Thanks for your help
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='CCJ' post='767375' date='25-Mar-2009 17:30']All the column (DC) titles are different. I want to search each column and
    return the results to a blank sheet in the workbook - return the column name
    and the most recent date where the result in the S/F field (there are 2) is
    "S".
    The first S/F field tells if something was successful on the first try; if
    it was not (status is F) and there may be a retry and that status is in the
    second S/F field.
    For example, on the attached sheet, LAN last date should be 031609, AUB is
    032109, CST is 030909, and WCH is 032109

    Results will just need the DC (LAN, AUB, CST, WCH) and the most recent date

    Thanks for your help[/quote]

    Attached is a possible solution
    Since the workbook you attached has the possible dates as Text I opened a new column and converted these to Excel dates. I also converted S to 1 and F to -0- but this step is not that necessary. Next there is new column that evaluates the date and if there was a success. This Col only allows those dates were it a success.

    Lastly at the top of the Column is a Maximum Function that find the largest number in the Column (always the most recent date) which should be your answer.

    From here all you need to do is create a new workheet and reference the item you want and the cell with the maximum function that refers to the item.


    Regards,

    Tom Duthie
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks much it worked perfectly.


    [quote name='duthiet' post='767396' date='25-Mar-2009 15:50']Attached is a possible solution
    Since the workbook you attached has the possible dates as Text I opened a new column and converted these to Excel dates. I also converted S to 1 and F to -0- but this step is not that necessary. Next there is new column that evaluates the date and if there was a success. This Col only allows those dates were it a success.

    Lastly at the top of the Column is a Maximum Function that find the largest number in the Column (always the most recent date) which should be your answer.

    From here all you need to do is create a new workheet and reference the item you want and the cell with the maximum function that refers to the item.


    Regards,

    Tom Duthie[/quote]

  4. #4
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I spoke too soon - Small problem - See Row 12 in your worksheet.
    The first set of S/F ((1/0) is fine but the sheet did not calculate the second S/F set also.
    So result is that it assumes a failure because the first try did not work but does not check the second S/F set to see if that was a success.
    Can the If statement be modified to check both fields?

    Thanks in advance I appreciate your help.

    [quote name='CCJ' post='767401' date='25-Mar-2009 16:02']Thanks much it worked perfectly.[/quote]

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='CCJ' post='767408' date='25-Mar-2009 19:24']I spoke too soon - Small problem - See Row 12 in your worksheet.
    The first set of S/F ((1/0) is fine but the sheet did not calculate the second S/F set also.
    So result is that it assumes a failure because the first try did not work but does not check the second S/F set to see if that was a success.
    Can the If statement be modified to check both fields?

    Thanks in advance I appreciate your help.[/quote]

    See the attached.

    Workbook now will find any date where the first or second test are sucessful.

    Regards,

    Tom Duthie
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just what I needed - thanks so much for your help.

    [quote name='duthiet' post='767420' date='25-Mar-2009 18:02']See the attached.

    Workbook now will find any date where the first or second test are sucessful.

    Regards,

    Tom Duthie[/quote]

Posting Permissions

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