Results 1 to 8 of 8

Thread: Validation for date required with lowest number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2015
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Validation for date required with lowest number

    Hi Team,

    I have below data, looking for a formula in column F to see which is the lowest number in column E for column A. Expected result provided in column F.

    Column A Column B Column C Column D Column E Column F
    Number name date todays date today date - date Result
    17137 BD 04/07/2019 30/07/2019 26 TRUE
    17137 BR 17/05/2019 30/07/2019 74
    17137 EP 04/07/2019 30/07/2019 26 TRUE
    17137 LD 04/07/2019 30/07/2019 26 TRUE
    17137 PA 14/06/2019 30/07/2019 46
    17137 TS 20/05/2019 30/07/2019 71
    18370 BB 20/05/2019 30/07/2019 71
    18370 BR 20/05/2019 30/07/2019 71
    18370 EP 20/05/2019 30/07/2019 71
    18370 LD 20/05/2019 30/07/2019 71
    18370 PA 19/06/2019 30/07/2019 41
    18370 TS 29/05/2019 30/07/2019 62 TRUE

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    try

    =IF(E3=MINIFS($E$3:$E$14,$A$3:$A$14,A3),TRUE,"")

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,880
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    MAXIFS and MINIFS is only available in latest versions of Excel
    Solution using MAXIFS in column F
    Alternative using AGGREGATE in column H

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Number name date todays date today date - date Result
    (MAXIFS)
    formula in F2 copied down Result
    (AGGREGATE)
    formula in F2 copied down
    2
    17137
    BD
    04/07/2019
    30/07/2019
    26
    TRUE
    =MINIFS(E:E,A:A,A2)=E2
    TRUE
    =AGGREGATE(15,6,E:E/(A:A=A2),1)=E2
    3
    17137
    BR
    17/05/2019
    30/07/2019
    74
    FALSE
    FALSE
    4
    17137
    EP
    04/07/2019
    30/07/2019
    26
    TRUE
    TRUE
    5
    17137
    LD
    04/07/2019
    30/07/2019
    26
    TRUE
    TRUE
    6
    17137
    PA
    14/06/2019
    30/07/2019
    46
    FALSE
    FALSE
    7
    17137
    TS
    20/05/2019
    30/07/2019
    71
    FALSE
    FALSE
    8
    18370
    BB
    20/05/2019
    30/07/2019
    71
    FALSE
    FALSE
    9
    18370
    BR
    20/05/2019
    30/07/2019
    71
    FALSE
    FALSE
    10
    18370
    EP
    20/05/2019
    30/07/2019
    71
    FALSE
    FALSE
    11
    18370
    LD
    20/05/2019
    30/07/2019
    71
    FALSE
    FALSE
    12
    18370
    PA
    19/06/2019
    30/07/2019
    41
    TRUE
    TRUE
    13
    18370
    TS
    29/05/2019
    30/07/2019
    62
    FALSE
    FALSE
    14
    Sheet: Sheet4

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    .. and another one that should work in all versions.

    Minimums

    ABCDEF
    1Numbernamedatetodays datetoday date - dateResult
    217137BD4/07/201930/07/201926TRUE
    317137BR17/05/201930/07/201974
    417137EP4/07/201930/07/201926TRUE
    517137LD4/07/201930/07/201926TRUE
    617137PA14/06/201930/07/201946
    717137TS20/05/201930/07/201971
    818370BB20/05/201930/07/201971
    918370BR20/05/201930/07/201971
    1018370EP20/05/201930/07/201971
    1118370LD20/05/201930/07/201971
    1218370PA19/06/201930/07/201941TRUE
    1318370TS29/05/201930/07/201962

    Spreadsheet Formulas
    CellFormula
    F2=IF(SUMPRODUCT(--(A$2:A$13=A2),--(E$2:E$13),"",TRUE)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Jul 30th, 2019 at 07:58 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    Hi Peter,

    Formula isnt working it says - The formula is missing an opening or closing parentthesis

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    Quote Originally Posted by exceluser9 View Post
    Hi Peter,

    Formula isnt working it says - The formula is missing an opening or closing parentthesis
    My mistake - the forum often cuts off formulas that contain an < sign. That F2 formula should have been

    =IF(SUMPRODUCT(--(A$2:A$13=A2),--(E$2:E$13<E2)),"",TRUE)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    Its working, thank you Peter

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Validation for date required with lowest number - Urgent

    Quote Originally Posted by exceluser9 View Post
    Its working, thank you Peter
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •