Validation for date required with lowest number

exceluser9

Active Member
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 AColumn BColumn CColumn DColumn EColumn F
Numbernamedatetodays datetoday date - dateResult
17137BD04/07/201930/07/201926TRUE
17137BR17/05/201930/07/201974
17137EP04/07/201930/07/201926TRUE
17137LD04/07/201930/07/201926TRUE
17137PA14/06/201930/07/201946
17137TS20/05/201930/07/201971
18370BB20/05/201930/07/201971
18370BR20/05/201930/07/201971
18370EP20/05/201930/07/201971
18370LD20/05/201930/07/201971
18370PA19/06/201930/07/201941
18370TS29/05/201930/07/201962TRUE

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

AlanY

Well-known Member
Re: Validation for date required with lowest number - Urgent

try

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

Yongle

Well-known Member
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
Numbernamedatetodays datetoday date - dateResult
(MAXIFS)
formula in F2 copied downResult
(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
 

Peter_SSs

MrExcel MVP, Moderator
Re: Validation for date required with lowest number - Urgent

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

Excel Workbook
ABCDEF
1Numbernamedatetodays datetoday date - dateResult
217137BD4/07/201930/07/201926
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
Minimums
 
Last edited:

exceluser9

Active Member
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
 

Peter_SSs

MrExcel MVP, Moderator
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
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)
 

exceluser9

Active Member
Re: Validation for date required with lowest number - Urgent

Its working, thank you Peter :)
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top