Earliest date with conditions

[hrachao]

Hi everyone,

Can't seem to make this work...

I have a table like this:

[TABLE="width: 215"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]invoice nr[/TD]
[TD]invoice date[/TD]
[/TR]
[TR]
[TD]Alfa[/TD]
[TD]1[/TD]
[TD]11-10-2018[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]23[/TD]
[TD]10-02-2017[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]24[/TD]
[TD]01-03-2015[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]58[/TD]
[TD]06-04-2016[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]59[/TD]
[TD]07-04-2017[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]60[/TD]
[TD]08-04-2018[/TD]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]70[/TD]
[TD]01-06-2014[/TD]
[/TR]
[TR]
[TD]Sigma[/TD]
[TD]91[/TD]
[TD]02-06-2017[/TD]
[/TR]
[TR]
[TD]Sigma[/TD]
[TD]92[/TD]
[TD]03-06-2016
[/TD]
[/TR]
</tbody>[/TABLE]


And I need to find the earliest invoice date per vendor:

[TABLE="width: 199"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]earliest invoice date[/TD]
[/TR]
[TR]
[TD]Alfa[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Sigma[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Ideas?

Thanks for the help!

Helena

 

[alansidman]

Re: Help - earliest date with conditions

https://www.contextures.com/excelminmaxfunction.html

 

[steve the fish]

Re: Help - earliest date with conditions

Like this:

=MIN(IF($A$2:$A$10=E1,$C$2:$C$10))

Enter CTRL-SHIFT-ENTER. Put a vendor to test in E1 and your table in A1:C10

 

[Peter_SSs]

Re: Help - earliest date with conditions

Options using newer functions ..
If you have the MINIFS function (available in Excel 365) you can use the column B formula.
If you have at least Excel 2010 you can use the column C formula.

Excel Workbook
	A	B	C
1	Vendor	invoice nr	invoice date
2	Alfa	1	11/10/2018
3	Beta	23	10/02/2017
4	Beta	24	1/03/2015
5	Delta	58	6/04/2016
6	Delta	59	7/04/2017
7	Delta	60	8/04/2018
8	Gama	70	1/06/2014
9	Sigma	91	2/06/2017
10	Sigma	92	3/06/2016
11
12
13	Vendor	earliest invoice date
14	Alfa	11/10/2018	11/10/2018
15	Beta	1/03/2015	1/03/2015
16	Delta	6/04/2016	6/04/2016
17	Gama	1/06/2014	1/06/2014
18	Sigma	3/06/2016	3/06/2016
Earliest Date

 

[sandy666]

Re: Help - earliest date with conditions

or you can try PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Vendor[/td][td=bgcolor:#5B9BD5]invoice nr[/td][td=bgcolor:#5B9BD5]invoice date[/td][td][/td][td=bgcolor:#70AD47]Vendor[/td][td=bgcolor:#70AD47]Min[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Alfa[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

11/10/2018​

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

11/10/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Beta[/td][td]

23​

[/td][td]

10/02/2017​

[/td][td][/td][td]Beta[/td][td]

01/03/2015​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Beta[/td][td=bgcolor:#DDEBF7]

24​

[/td][td=bgcolor:#DDEBF7]

01/03/2015​

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

06/04/2016​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Delta[/td][td]

58​

[/td][td]

06/04/2016​

[/td][td][/td][td]Gama[/td][td]

01/06/2014​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Delta[/td][td=bgcolor:#DDEBF7]

59​

[/td][td=bgcolor:#DDEBF7]

07/04/2017​

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

03/06/2016​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Delta[/td][td]

60​

[/td][td]

08/04/2018​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Gama[/td][td=bgcolor:#DDEBF7]

70​

[/td][td=bgcolor:#DDEBF7]

01/06/2014​

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

[tr=bgcolor:#FFFFFF][td]Sigma[/td][td]

91​

[/td][td]

02/06/2017​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sigma[/td][td=bgcolor:#DDEBF7]

92​

[/td][td=bgcolor:#DDEBF7]

03/06/2016​

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

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

 

[hrachao]

Re: Help - earliest date with conditions

Thanks everyone! The formulas you guys presented work on the simplified table i gave as example, but not when I apply it to my real example... I still can't figure out why... trying to understand now (I get 00-01-1900) :S

 

[hrachao]

Re: Help - earliest date with conditions

It's working now!

 

[Peter_SSs]

Re: Help - earliest date with conditions

It's working now!

Glad you got it sorted.