ExcelBanter - Retrieving data based on most recent date

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Retrieving data based on most recent date (https://www.excelbanter.com/excel-worksheet-functions/174373-retrieving-data-based-most-recent-date.html)

Retrieving data based on most recent date

I have two sheets. On one sheet I have products and inventory taken on
different days:

A1 B1 C1
05/08/07 Product1 1300
05/08/07 Product2 560
30/08/07 Product1 685
30/08/07 Product3 1513
05/09/07 Product3 669
05/09/07 Product1 1754
05/09/07 Product2 555

On sheet two I want to retrieve the most recent inventory for each product
as well as the corresponding date.

A1 B1 C1
Product1 1754 05/09/07
Product2 555 05/09/07
Product3 669 05/09/07

Does anyone know what formulas I would use to do this? I'm using Excel 2000.

Retrieving data based on most recent date

Sheet 2:
C2: =SUMPRODUCT(--(MAX((Sheet1!$B$2:$B$8=A2),Sheet1!$A$2:$A$8)))
copy down

B2:
=SUMPRODUCT(--(Sheet1!$B$2:$B$8=A2),--(Sheet1!$A$2:$A$8=C2),Sheet1!$C$2:$C$8)
copy down

"cdoch" wrote:

I have two sheets. On one sheet I have products and inventory taken on
different days:

A1 B1 C1
05/08/07 Product1 1300
05/08/07 Product2 560
30/08/07 Product1 685
30/08/07 Product3 1513
05/09/07 Product3 669
05/09/07 Product1 1754
05/09/07 Product2 555

On sheet two I want to retrieve the most recent inventory for each product
as well as the corresponding date.

A1 B1 C1
Product1 1754 05/09/07
Product2 555 05/09/07
Product3 669 05/09/07

Does anyone know what formulas I would use to do this? I'm using Excel 2000.