looking for the location at the latest date

  • Thread starter Thread starter Geoffreycheung
  • Start date Start date
G

Geoffreycheung

Please help!

I want to update the location in sheet 1 automatically for the items in
column A at the latest date shown in column B in sheet 2. New transactions
will be recorded in sheet 2 manually.
For example:

In Sheet 1 shows the location of all items at the latest date.
ColumnA ColumnB
Item# Location
M-123 BC
M-245 NY
M-234 LA

In sheet 2 recording the transactions of item movment.
Column A Column B Column C
Item# Date Location
M-123 2003/10/3 NY
M-234 2003/10/4 LA
M-123 2003/10/7 HK
M-245 2003/10/9 FR
M-123 2003/10/20 BC
M-245 2003/11/1 NY

Thanks in advance.

KY
 
One way

=INDEX(Sheet2!$A$2:$A$50,MATCH(LARGE(Sheet2!$B$2:$B$50,ROW(1:1)),Sheet2!$B$2
:$B$7,0))

copy down assuming the Item# is in Sheet2 column A and for the location

=INDEX(Sheet2!$C$2:$C$50,MATCH(LARGE(Sheet2!$B$2:$B$50,ROW(1:1)),Sheet2!$B$2
:$B$7,0))

copied down
 
Peo Sjoblom,

Thank you for your prompt reply

To be more clarify, the location in sheet 1 should be shown according to the
rank of item# in column A of sheet 1 instead of the date in sheet 2.

Best Regards,
KY
 
In Sheet1,
1. Enter the following array formula into Cell C2.
{=MAX((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50))}
2. Enter the following formula into Cell B1.
=INDEX(Sheet2!C$2:C$50,SUMPRODUCT((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50=Sheet1!C2)*(ROW(Sheet2!B$2:B$50)))-1,1)
3. Copy Range B2:C2 downward.
 
Thank you for your time so much, but the first array formula did not work,
the outcome is not the latest date of item in column A.

Please help.

Best Rgds,
KY


Mike said:
In Sheet1,
1. Enter the following array formula into Cell C2.
{=MAX((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50))}
2. Enter the following formula into Cell B1.
=INDEX(Sheet2!C$2:C$50,SUMPRODUCT((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$
50=Sheet1!C2)*(ROW(Sheet2!B$2:B$50)))-1,1)
3. Copy Range B2:C2 downward.
Click to expand...
news:[email protected]...
 
Assuming that A1:C7 in Sheet2 houses the transactions of item movement
including the labels (Item#, etc.).
In D1 enter: Concat [ which is just a label ]
In D2 on Sheet2 enter & copy down.

=A2&CHAR(127)&B2

Assuming that A1:A4 on Sheet1 houses the lookup values, including the label
Item#.

In B1 on Sheet1 enter:

=INDEX(Sheet2!$C$2:$C$7,MATCH(A2&CHAR(127)&MAX((A2=Sheet2!$A$2:$A$7)*(Sheet2
!$B$2:$B$7)),Sheet2!$D$2:$D$7,0))

which must be confirmed with control+shift+enter, not just with enter.

Copy down B1 for the rest of the lookup values.
 
You should have emailed your file to me, not uploaded to this newsgroup.
Anyway ....

1. You entered the "almost" correct formula into Cell C2 as follows;
=MAX((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50))
But it is not complete.
Select Cell C2 and move your mouse pointer into [Formula Bar].
Hoding Ctrl and Shift keys depressed together, hit Enter key.
And you will see the formula change to;
{=MAX((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50))}

2. You entered the wrong formula into Cell B2 as follows;
=INDEX(Sheet2!C$2:C$50,SUMPRODUCT((Sheet2!A$2:A$50=Sheet1!A2)*(Sheet2!B$2:B$50=Sheet1!C2)*(ROW(Sheet2!B$2:B$50)))*1,1)
Change 5th character from the end from *(asterisk) to -(minus).

3. Copy Range B2:C2 downward.
 
Back
Top