Most recent receipt date for an item

P

Pierre

Col A has a slew of part numbers with many of them repeated; Col B has
a transaction date for that item.
On sheet 2, there's the list of items which appear in colum A on sheet
1 (none repeated on sheet 2). I'd like to populate ColumnB on sheet 2
with the most recent transaction date that matches up with the most
recent transaction date in column B on sheet 1.

Ideas?

Pierre
 
M

Max

In Sheet2,

With unique part numbers in A2 down

In B2, array-entered**:
=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$B$100))
Format B2 as date, copy down
ADapt the ranges to suit

**Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
 
P

Pierre

In Sheet2,

With unique part numbers in A2 down

In B2, array-entered**:
=MAX(IF(Sheet1!$A$2:$A$100=A2,Sheet1!$B$2:$B$100))
Format B2 as date, copy down
ADapt the ranges to suit

**Press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
Click to expand...


Max,
looks great and works like a charm. Thanks much.
Pierre
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot table: Most recent price for an item? 3
Criteria to return maximum value where x = x 3
Copy from sheet to sheet when matches criteria 2
Produce the most recent date 6
Lookup function 2
Question: How many occurences in date range? 4
if function (if more that 7 nesting) 2
Removal Date 6

Top