Minimum of the multiple dates for a Product

[soumen21]

Hi,

I have a list of products and associated dates. A product will have multiple materials and each material have a expiry date. Something like table below.

[TABLE="width: 224"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Material[/TD]
[TD]Material Expiry[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]a[/TD]
[TD]16-Aug-23[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]b[/TD]
[TD]17-May-23[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]b[/TD]
[TD]18-Aug-25[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]c[/TD]
[TD]19-Aug-22[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]b[/TD]
[TD]20-Aug-26[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]d[/TD]
[TD]21-Feb-20[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]a[/TD]
[TD]22-Aug-24[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]a[/TD]
[TD]23-Dec-19[/TD]
[/TR]
</tbody>[/TABLE]

I want to run a query where say I want to look for product 'AA' and say its expiry date. For example 'AA' has material 'd' whose expiry date is 21-Feb-20. So for query for expiry of product AA, I should get the earliest of all dates, in this case 21-Feb-20.
Could you please help with the excel formula to find this out?

Regards
Soumen

 

[sandy666]

you can try PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Material", type text}, {"Material Expiry", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Min Date", each List.Min([Material Expiry]), type date}})
in
    #"Grouped Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Product[/td][td=bgcolor:#5B9BD5]Material[/td][td=bgcolor:#5B9BD5]Material Expiry[/td][td][/td][td=bgcolor:#70AD47]Product[/td][td=bgcolor:#70AD47]Min Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]

16/08/2023​

[/td][td][/td][td=bgcolor:#E2EFDA]AA[/td][td=bgcolor:#E2EFDA]

21/02/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA[/td][td]b[/td][td]

17/05/2023​

[/td][td][/td][td]BB[/td][td]

22/08/2024​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]b[/td][td=bgcolor:#DDEBF7]

18/08/2025​

[/td][td][/td][td=bgcolor:#E2EFDA]CC[/td][td=bgcolor:#E2EFDA]

23/12/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA[/td][td]c[/td][td]

19/08/2022​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]b[/td][td=bgcolor:#DDEBF7]

20/08/2026​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA[/td][td]d[/td][td]

21/02/2020​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]

22/08/2024​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]CC[/td][td]a[/td][td]

23/12/2019​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Peter_SSs]

Could you please help with the excel formula to find this out?

Is this what you mean?

Excel Workbook
	A	B	C	D	E	F
1	Product	Material	Material Expiry		Product	AA
2	AA	a	16-Aug-23		Earliest Expiry	21-Feb-20
3	AA	b	17-May-23
4	BB	b	18-Aug-25
5	AA	c	19-Aug-22
6	CC	b	20-Aug-26
7	AA	d	21-Feb-20
8	BB	a	22-Aug-24
9	CC	a	23-Dec-19
Earliest Date

 

[soumen21]

Yes, Thanks a lot Peter. Highly appreciated.

 

[soumen21]

Thank you so much Sandy

 

[Peter_SSs]

Yes, Thanks a lot Peter. Highly appreciated.

You are very welcome.
BTW, welcome to the MrExcel board!

 

[sandy666]

Thank you so much Sandy

You are most welcome

Have a nice day

 

[soumen21]

I want to add one of column with "status" as open or close. If the status is open then only the earliest of the date will be shown. As per example, as per the table below

[TABLE="width: 305"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Status[/TD]
[TD]Product[/TD]
[TD]Material Expiry[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]AA[/TD]
[TD]16-Aug-19[/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]AA[/TD]
[TD]17-May-23[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]BB[/TD]
[TD]18-Aug-25[/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]AA[/TD]
[TD]19-Aug-22[/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]CC[/TD]
[TD]20-Aug-26[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]AA[/TD]
[TD]21-Feb-20[/TD]
[/TR]
[TR]
[TD]Open[/TD]
[TD]BB[/TD]
[TD]22-Aug-24[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]CC[/TD]
[TD]23-Dec-19[/TD]
[/TR]
</tbody>[/TABLE]

Product AA will show earliest date as 19-Aug-22 (not 21-Feb-20) among the earliest of the dates as the status is still open.

 

[Fluff]

Expanding on Peter's formula

Book1
	A	B	C	D	E	F
1	Status	Product	Material Expiry		Product	AA
2	Closed	AA	16-Aug-19		Earliest Expiry	19-Aug-22
3	Open	AA	17-May-23
4	Closed	BB	18-Aug-25
5	Open	AA	19-Aug-22
6	Open	CC	20-Aug-26
7	Closed	AA	21-Feb-20
8	Open	BB	22-Aug-24
9	Closed	CC	23-Dec-19
Norfolk

Cell Formulas
Range	Formula
F2	=AGGREGATE(15,6,$C$2:$C$9/(($B$2:$B$9=F1)*(A2:A9="Open")),1)

 

[soumen21]

Thank you so much Fluff.