Excel2000: The latest value

[Arvi Laanemets]

Hi

I have a workbook where various items (computers, monitors, printers, etc.)
are registered. Every item has a unique ID in format "Cyyyymmdd###" (Item
group+purchase date+3-character number. For every item group (a different
1st character) exists a separate sheet with different column collection. In
every of those tables the leftmost column (A:A) is ItemID, and there exists
a column User in every table.

On additional sheet (let it be Movements) all user changes are registered
RecNo, ItemGroup, ItemID, Date, User
<RecNo, p.e. A2=IF(C2="","",ROW()-1)> is calculated automatically

On item sheets, I need the latest user (with latest date for this item) from
Movements for every item to be displayed. The problem is, that the return
value is string - so SUMPRODUCT is not an option.


Thanks in advance for any help

 

[Bob Phillips]

Arvi,

I am not sure that I completely get the layout, but could you use something
like

=MAX(IF(Movements!$A$2:$A$200=A2,Movements!$C$2:$C$200))

would get the latest date for that item, assuming that column C is the date
column

Then use, this to get the user with

=INDEX(Movements!$B$2:$B$200,MATCH(A2&latest_date,Movements!$A$2:$A$200&Move
ments!$C$2:$C$200,0))

or in one formula

=INDEX(Movements!$B$2:$B$200,MATCH(A2&MAX(IF(Movements!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Movements!$C$2:$C$200,0))

all array formulae

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

The layout on Movements sheet is like this
RecNo ItemGroup ItemID Date User
1 computer C20030101001 01.01.2005 User1
2 monitor M20041128003 01.01.2005 User1
3 computer C20050201012 17.01.2005 User2
4 monitor M20020306001 17.01.2005 User2
5 monitor M20041128003 21.03.2005 User3
6 monitor M20050321001 21.03.2005 User1
7 computer C20050321001 21.03.2005 User3
8 computer C20050321002 21.03.2005 User4
9 monitor M20050321002 21.03.2005 User4
....

Now, on items sheets, I need returned:
for C20030101001 - User1
for C20050201012 - User2
for C20050321001 - User3
for C20050321002 - User4
for M20041128003 - User3
for M20020306001 - User2
for M20050321001 - User1
for M20050321002 - User4
.... etc

I.e. I need RecNo on Movements sheet, where ItemID equals with searched
ItemID, and the Date is greatest for searched ItemID.
There will never be more than one row for same item on same date in
Movements table, but there can be any number of entries for different items
on same date.

 

[Bob Phillips]

=INDEX(Movements!$E$2:$E$200,MATCH(A1&MAX(IF(Movements!$C$2:$C$200=A1,Moveme
nts!$D$2:$D$200)),Movements!$C$2:$C$200&Movements!$D$2:$D$200,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi again

I get your formula working now
=IF(MAX(IF(MoveItem=A2,MoveNo))=0,"",INDEX(MoveUser,MAX(IF(MoveItem=A2,MoveNo))))
entered as array function, and using single-column dynamic named ranges.


Thanks!
(But maybe I have to write an UDF anyway - I'll see how having a lot off
array formulas in workbook affects the perfomance)

 

[Bob Phillips]

But a lot of UDFs will also affect performance.

--

HTH

RP
(remove nothere from the email address if mailing direct)