Earliest date with conditions

[hrachao]

Hi everyone,

Can't seem to make this work...

I have a table like this:

Vendor	invoice nr	invoice date
Alfa	1	11-10-2018
Beta	23	10-02-2017
Beta	24	01-03-2015
Delta	58	06-04-2016
Delta	59	07-04-2017
Delta	60	08-04-2018
Gama	70	01-06-2014
Sigma	91	02-06-2017
Sigma	92	03-06-2016

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

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

Vendor	earliest invoice date
Alfa	?
Beta	?
Delta	?
Gama	?
Sigma	?

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

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

Vendor	invoice nr	invoice date		Vendor	Min
Alfa	1​	11/10/2018​		Alfa	11/10/2018​
Beta	23​	10/02/2017​		Beta	01/03/2015​
Beta	24​	01/03/2015​		Delta	06/04/2016​
Delta	58​	06/04/2016​		Gama	01/06/2014​
Delta	59​	07/04/2017​		Sigma	03/06/2016​
Delta	60​	08/04/2018​
Gama	70​	01/06/2014​
Sigma	91​	02/06/2017​
Sigma	92​	03/06/2016​

[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.