Excel2000: The latest value
 

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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Excel2000: The latest value
 

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(Movem ents!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Mov ements!$C$2:$C$200,0))

all array formulae

--

HTH

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


"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Excel2000: The latest value
 

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.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

"Bob Phillips" wrote in message
...
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(Movem ents!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Mov ements!$C$2:$C$200,0))

all array formulae

--

HTH

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


"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Excel2000: The latest value
 

=INDEX(Movements!$E$2:$E$200,MATCH(A1&MAX(IF(Movem ents!$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" wrote in message
...
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.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

"Bob Phillips" wrote in message
...
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(Movem ents!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Mov ements!$C$2:$C$200,0))

all array formulae

--

HTH

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


"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Excel2000: The latest value
 

Hi again

I get your formula working now
=IF(MAX(IF(MoveItem=A2,MoveNo))=0,"",INDEX(MoveUse r,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)

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Bob Phillips" wrote in message
...
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(Movem ents!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Mov ements!$C$2:$C$200,0))

all array formulae

--

HTH

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


"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Excel2000: The latest value
 

But a lot of UDFs will also affect performance.

--

HTH

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


"Arvi Laanemets" wrote in message
...
Hi again

I get your formula working now

=IF(MAX(IF(MoveItem=A2,MoveNo))=0,"",INDEX(MoveUse r,MAX(IF(MoveItem=A2,MoveN
o))))
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)

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Bob Phillips" wrote in message
...
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(Movem ents!$A$2:$A$200=Sheet1!A2
,Movements!$C$2:$C$200)),Movements!$A$2:$A$200&Mov ements!$C$2:$C$200,0))

all array formulae

--

HTH

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


"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )