Validation for date required with lowest number

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
362
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>
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Mar 11, 2015
Messages
5,249
Office Version
365
Platform
Windows
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
Joined
May 28, 2005
Messages
43,402
Office Version
365
Platform
Windows
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/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
Minimums
 
Last edited:

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
362
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
Joined
May 28, 2005
Messages
43,402
Office Version
365
Platform
Windows
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
Joined
Jun 27, 2015
Messages
362
Re: Validation for date required with lowest number - Urgent

Its working, thank you Peter :)
 

Forum statistics

Threads
1,089,210
Messages
5,406,868
Members
403,109
Latest member
gamer527

This Week's Hot Topics

Top