Macro to take most recent date in long list?

J

Jennifer

Okay I have a spread sheet with the follwing columns:

Item Warehouse Last Sale
A 1 50129
A 1 60625
A 2 51001
A 2 40329
B 1 60515
etc.

Can I create a macro that will give me the last Sale for each item in each
WH? For example - Item A, WH 1 - 60625, Item A, WH 2 - 51001? I can put the
data anywhere (new worksheet, new column, etc)

Or maybe I don't need a macro - maybe there is an easier way?

TIA for any help!
 
B

Bob Phillips

You can do it with an array formula

=INDEX(B1:B20,MAX(IF($A$1:$A$20="A",ROW($A$1:$A$20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

copy across for the last sale

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
B

Bernie Deitrick

Jennifer,

Forget formulas. Use a pivot table: select your data table, choose Data / Pivot table, then
clikc through to OK. Drag the Item to the row field, the Warehouse to the row field, and Last Sale
to the data field, and choose Max from the field setting dialog box. No formulas at all, and you
will get every combination of item and warehouse.

HTH,
Bernie
MS Excel MVP
 
Top