Results 1 to 2 of 2

Thread: Finding "oldest outstanding" item
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Finding "oldest outstanding" item

    Hi all, I'm after some more help with my spreadsheet - this time with finding some of the oldest outstanding of particular items

    The spreadsheet below is an exert from the Master. I need to show the oldest date of items yet to be completed which is displayed in a 'metrics' table on another worksheet.

    For example, I want it to generate the oldest, yet to be completed QUOTE/SPEC REQUEST/ GENERAL ENQUIRY

    Is this a V look up or something else?

    Thanks in advance for any help!


    Received Via Date received in CC Type of request Status Date completed
    Direct email to CC 14/11/2016 SPEC REQUEST Complete 14/11/2016
    14/11/2016 GENERAL ENQUIRY Complete
    Customer 14/11/2016 GENERAL ENQUIRY Complete 14/11/2016
    14/11/2016 GENERAL ENQUIRY Complete 15/11/2016
    Direct email to CC 14/11/2016 QUOTE REQUST In progress
    Direct email to CC 15/11/2016 COMPLAINT In progress
    Direct email to CC 15/11/2016 SPEC REQUEST With Dean H
    Online enquiry 16/11/2016 QUOTE REQUST Complete 16/11/2016
    Ralf 16/11/2016 SPEC REQUEST Complete 17/11/2016
    Marc 16/11/2016 SAMPLE REQUEST In progress
    Direct email to CC 16/11/2016 COMPLAINT With Quality
    Direct email to CC 17/11/2016 GENERAL ENQUIRY Complete

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Finding "oldest outstanding" item

    Hi,

    Check if this what you're after:

    Excel 2016 (Windows) 64 bit
    ABCDE
    1Received ViaDate received in CCType of requestStatusDate completed
    2Direct email to CC14-11-2016SPEC REQUESTComplete14-nov-16
    314-nov-16GENERAL ENQUIRYComplete
    4Customer14-11-2016GENERAL ENQUIRYComplete14-11-2016
    514-nov-16GENERAL ENQUIRYComplete15-11-2016
    6Direct email to CC14-11-2016QUOTE REQUSTIn progress
    7Direct email to CC15-11-2016COMPLAINTIn progress
    8Direct email to CC15-11-2016SPEC REQUESTWith Dean H
    9Online enquiry16-11-2016QUOTE REQUSTComplete16-11-2016
    10Ralf16-11-2016SPEC REQUESTComplete17-11-2016
    11Marc16-11-2016SAMPLE REQUESTIn progress
    12Direct email to CC16-11-2016COMPLAINTWith Quality
    13Direct email to CC17-11-2016GENERAL ENQUIRYComplete
    14
    15
    16QUOTE REQUST14-11-2016
    17SPEC REQUEST15-11-2016
    18GENERAL ENQUIRY

    Sheet1



    Array Formulas
    CellFormula
    B16{=IF(MIN(IF((D2:D13<>"Complete")*(C2:C13=A16),B2:B13,""))=0,"",MIN(IF((D2:D13<>"Complete")*(C2:C13=A16),B2:B13,"")))}
    B17{=IF(MIN(IF((D2:D13<>"Complete")*(C2:C13=A17),B2:B13,""))=0,"",MIN(IF((D2:D13<>"Complete")*(C2:C13=A17),B2:B13,"")))}
    B18{=IF(MIN(IF((D2:D13<>"complete")*(C2:C13=A18),B2:B13,""))=0,"",MIN(IF((D2:D13<>"complete")*(C2:C13=A18),B2:B13,"")))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    nb: beware of the typo in "quote requst"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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