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.