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

Top