Minimum Date if cell contains part of another cell value

  • Hi,


    This has been driving me nuts, please can someone take a look.


    Column U contains unique codes made up of parent codes and child codes as such:


    [TABLE="width: 500"]

    [tr]


    [td]

    Code (Col U)

    [/td]


    [td]

    Parent

    [/td]


    [td]

    Date

    [/td]


    [td]

    Minimum

    [/td]


    [/tr]


    [tr]


    [td]

    TEST100

    [/td]


    [td]

    1

    [/td]


    [td]

    10-08-16

    [/td]


    [td]

    08-08-16

    [/td]


    [/tr]


    [tr]


    [td]

    TEST100.10

    [/td]


    [td][/td]


    [td]

    08-08-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST100.20

    [/td]


    [td][/td]


    [td]

    25-09-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST200

    [/td]


    [td]

    1

    [/td]


    [td]

    26-10-16

    [/td]


    [td]

    26-10-16

    [/td]


    [/tr]


    [tr]


    [td]

    TEST200.10

    [/td]


    [td][/td]


    [td]

    28-10-16

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TEST200.20

    [/td]


    [td][/td]


    [td]

    10-11-16

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    It is the minimum column I am trying to achieve.


    What I need is:


    IF parent THEN search all codes in U6:U for codes containing the parent and return the minimum date.


    The closest i have gotten is:


    MIN(AB6:INDEX(OFFSET($U$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1),MATCH(TRUE,(OFFSET($A$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1)=1),0))),"")


    A = parent column
    U = code column
    AB = dates


    I would much appreciate some help here - thanks.

  • Re: Minimum Date if cell contains part of another cell value


    Try this:


    =IF(A2<>"",MINIFS($AB$2:$AB$7,$U$2:$U$7,U2&"*"),"")


    Example:


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]A[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]B[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]C[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]D[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]1[/COLOR]

    [/td]

    [td]

    Code (Col U)

    [/td][td]

    Parent

    [/td][td]

    Date

    [/td][td]

    Minimum

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    TEST100

    [/td][td]

    1

    [/td][td]

    10/08/2016

    [/td][td]

    08/08/2016

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    TEST100.10

    [/td][td]

    [/td][td]

    08/08/2016

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]4[/COLOR]

    [/td]

    [td]

    TEST100.20

    [/td][td]

    [/td][td]

    25/09/2016

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]5[/COLOR]

    [/td]

    [td]

    TEST200

    [/td][td]

    1

    [/td][td]

    26/10/2016

    [/td][td]

    26/10/2016

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]6[/COLOR]

    [/td]

    [td]

    TEST200.10

    [/td][td]

    [/td][td]

    28/10/2016

    [/td][td]

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]7[/COLOR]

    [/td]

    [td]

    TEST200.20

    [/td][td]

    [/td][td]

    10/11/2016

    [/td][td]

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet2

    [/td][/tr]

    [/table]


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]D[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    =IF([COLOR="#0000FF"]B2<>"",MINIFS([COLOR="#FF0000"]$C$2:$C$7,$A$2:$A$7,A2&"*"[/COLOR]),""[/COLOR])

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet2

    [/td][/tr]

    [/table]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Minimum Date if cell contains part of another cell value


    Ali - thank you for taking the time to reply.


    Unfortunately this is giving me a NAME error - is this function compatible with excel 2010?


    Any suggestions?


    Also, I would like $C$7 and $A$7 in your example above to be open ended which is why I was trying to implement OFFSET($U$6,0,0,COUNTA($U:$U)-COUNTA($U$1:$U$5),1) into the solution.

  • Re: Minimum Date if cell contains part of another cell value


    No, it's not, but we can get round that.


    You can make it "open-ended" by using C:C and A:A respectively.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Minimum Date if cell contains part of another cell value


    Here you go:


    =IF(B2<>"",MIN(IF(ISNUMBER(SEARCH(A2,A:A)),C:C)),"")


    In other words:


    =IF(A2<>"",MIN(IF(ISNUMBER(SEARCH(U2,U:U)),AB:AB)),"")


    It's an array formula, so needs to be entered with CTRL+SHIFT+ENTER. Just change the ranges to suit.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Minimum Date if cell contains part of another cell value


    Ali,


    Just one more thing - the sample data given has no blanks. I think this is the problem at my end with the real set of data - min is always showing as 00-01-00 when a blank is in the range.


    Sorry to be a pain

  • Re: Minimum Date if cell contains part of another cell value


    OK - I'm afraid you really should provide sample data that is truly representative to avoid this! Give me a mo. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Minimum Date if cell contains part of another cell value


    Right - last attempt! Try this:


    =IF(A2<>"",MIN(IF(ISNUMBER(SEARCH(U2,U:U)),IF(AB:AB<>"",AB:AB))),"")


    confirmed with C+S+E.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Minimum Date if cell contains part of another cell value


    You are very welcome. We got there in the end! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!