How to find most items with most recent date?

[JenniferMurphy]

I have a table with a record for each time I have purchased various products. I'd like to create a second table that has just one record for each item with columns for the number of times it was purchased, the average price, and the date of the most recent purchase. I have the first two, but am stuck on how to get the most recent date.

I would post a mini-sheet, but I am having trouble installing xl2bb on this new laptop.

Thanks

 

[Tetra201]

Try using MAXIFS.

 

[kevin9999]

Like this:

Book1
	A	B	C	D	E	F	G	H
1	Product	Price	Date		ProductID	#times	Avg $	Most recent
2	Product 1	$25.00	10/07/2023		Product 1	3	$24.00	18/07/2023
3	Product 2	$23.00	11/07/2023		Product 2	3	$17.33	19/07/2023
4	Product 3	$25.00	12/07/2023		Product 3	3	$19.67	20/07/2023
5	Product 4	$4.00	13/07/2023		Product 4	3	$4.33	21/07/2023
6	Product 1	$24.00	14/07/2023
7	Product 2	$4.00	15/07/2023
8	Product 3	$21.00	16/07/2023
9	Product 4	$6.00	17/07/2023
10	Product 1	$23.00	18/07/2023
11	Product 2	$25.00	19/07/2023
12	Product 3	$13.00	20/07/2023
13	Product 4	$3.00	21/07/2023
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=COUNTIF(A:A,E2)
G2:G5	G2	=AVERAGEIF(A:A,E2,B:B)
H2:H5	H2	=MAXIFS(C:C,A:A,E2)

 

[JenniferMurphy]

Like this:

Perfect. I was using CountIfs and SumIfs (now replaced by AverageIfs), but stupidly did not think to try MaxIfs. I did a search, but all I found was array solutions.

Thank you!

 

[kevin9999]

Perfect. I was using CountIfs and SumIfs (now replaced by AverageIfs), but stupidly did not think to try MaxIfs. I did a search, but all I found was array solutions.

Thank you!

Happy to help Jennifer, and thanks for the feedback

 

[alansidman]

I know you have a solution, but I wanted to show you an altercative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Average Price", each List.Average([Price]), type nullable number}, {"Last Date", each List.Max([Date]), type nullable date}})
in
    #"Grouped Rows"

Book6
	A	B	C	D	E	F	G	H
1	Product	Price	Date		Product	Count	Average Price	Last Date
2	Product 1	25	7/10/2023		Product 1	3	24	7/18/2023
3	Product 2	23	7/11/2023		Product 2	3	17.33333333	7/19/2023
4	Product 3	25	7/12/2023		Product 3	3	19.66666667	7/20/2023
5	Product 4	4	7/13/2023		Product 4	3	4.333333333	7/21/2023
6	Product 1	24	7/14/2023
7	Product 2	4	7/15/2023
8	Product 3	21	7/16/2023
9	Product 4	6	7/17/2023
10	Product 1	23	7/18/2023
11	Product 2	25	7/19/2023
12	Product 3	13	7/20/2023
13	Product 4	3	7/21/2023
Sheet1

 

[JenniferMurphy]

I know you have a solution, but I wanted to show you an altercative with Power Query

I don't know anything about Power Query. Can you tell me how it is enough better for this application than MaxIfs to justify an add-in and code?

Thanks

 

[alansidman]

It is an alternative. However, if you have a continuous list that is ever expanding, then PQ will automatically update when the new data is added and only require you to refresh the worksheet. As you are aware, there are many ways to solve issues in Excel and this is one. It should be noted that it is not an add in for your version and is called Get and Transform Data and found on the Data Tab of the Ribbon. Since this is a public forum database, people look for answers without posting, this is just another means to the end. If anyone is interested in the benefits of PQ, then a link in my signature is a good starting point.

 

[JenniferMurphy]

It is an alternative. However, if you have a continuous list that is ever expanding, then PQ will automatically update when the new data is added and only require you to refresh the worksheet. As you are aware, there are many ways to solve issues in Excel and this is one. It should be noted that it is not an add in for your version and is called Get and Transform Data and found on the Data Tab of the Ribbon. Since this is a public forum database, people look for answers without posting, this is just another means to the end. If anyone is interested in the benefits of PQ, then a link in my signature is a good starting point.

Ok, good point.