Vlookup !
 

Hello All,
I am looking some following solution :

Sheet1 Table :
Col_A (Date) Col_B (Name) Col_C (Amt)
01-Jan-2009 Japan 1500
02-Jan-2009 Singapore 2000
05-Jan-2009 Singapore 1700
06-Jan-2009 Japan 1000
10-Jan-2009 Korea 1500

Now I want get in Sheet2 just last date & Pmt Amt like as under :
Japan 06-Jan-2009 1000
Singapore 05-Jan-2009 1700

Is it possible ?

Thanks in advance.

Vlookup !
 

Assuming you have the data in Sheet1 starting from row1 to row10
In sheet2
A1 = Japan (or any other query text)
B1 =SUMPRODUCT(MAX((Sheet1!$B$1:$B$10=A1)*(Sheet1!$A$ 1:$A$10)))
C1 =
SUMPRODUCT(--(Sheet1!$A$1:$A$10=B1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1!$C$1:$C$10))

Copy the formula in one line...and copy it down..

If this post helps click Yes
---------------
Jacob Skaria


"Tufail" wrote:

Hello All,
I am looking some following solution :

Sheet1 Table :
Col_A (Date) Col_B (Name) Col_C (Amt)
01-Jan-2009 Japan 1500
02-Jan-2009 Singapore 2000
05-Jan-2009 Singapore 1700
06-Jan-2009 Japan 1000
10-Jan-2009 Korea 1500

Now I want get in Sheet2 just last date & Pmt Amt like as under :
Japan 06-Jan-2009 1000
Singapore 05-Jan-2009 1700

Is it possible ?

Thanks in advance.

Vlookup !
 

Also format Sheet2 date field B1,b2...to date format
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Assuming you have the data in Sheet1 starting from row1 to row10
In sheet2
A1 = Japan (or any other query text)
B1 =SUMPRODUCT(MAX((Sheet1!$B$1:$B$10=A1)*(Sheet1!$A$ 1:$A$10)))
C1 =
SUMPRODUCT(--(Sheet1!$A$1:$A$10=B1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1!$C$1:$C$10))

Copy the formula in one line...and copy it down..

If this post helps click Yes
---------------
Jacob Skaria


"Tufail" wrote:

Hello All,
I am looking some following solution :

Sheet1 Table :
Col_A (Date) Col_B (Name) Col_C (Amt)
01-Jan-2009 Japan 1500
02-Jan-2009 Singapore 2000
05-Jan-2009 Singapore 1700
06-Jan-2009 Japan 1000
10-Jan-2009 Korea 1500

Now I want get in Sheet2 just last date & Pmt Amt like as under :
Japan 06-Jan-2009 1000
Singapore 05-Jan-2009 1700

Is it possible ?

Thanks in advance.

Vlookup !
 

thank you very much and so kind of you always :)

"Jacob Skaria" wrote:

Also format Sheet2 date field B1,b2...to date format
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Assuming you have the data in Sheet1 starting from row1 to row10
In sheet2
A1 = Japan (or any other query text)
B1 =SUMPRODUCT(MAX((Sheet1!$B$1:$B$10=A1)*(Sheet1!$A$ 1:$A$10)))
C1 =
SUMPRODUCT(--(Sheet1!$A$1:$A$10=B1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1!$C$1:$C$10))

Copy the formula in one line...and copy it down..

If this post helps click Yes
---------------
Jacob Skaria


"Tufail" wrote:

Hello All,
I am looking some following solution :

Sheet1 Table :
Col_A (Date) Col_B (Name) Col_C (Amt)
01-Jan-2009 Japan 1500
02-Jan-2009 Singapore 2000
05-Jan-2009 Singapore 1700
06-Jan-2009 Japan 1000
10-Jan-2009 Korea 1500

Now I want get in Sheet2 just last date & Pmt Amt like as under :
Japan 06-Jan-2009 1000
Singapore 05-Jan-2009 1700

Is it possible ?

Thanks in advance.

Vlookup !
 

Try this:

In sheet 2
A2: holds criteria

Date
B2: =LOOKUP(2,1/(Name=A2),Date)

Amount
C2: =LOOKUP(2,1/(Name=A2),Amount)




"Tufail" wrote:

Hello All,
I am looking some following solution :

Sheet1 Table :
Col_A (Date) Col_B (Name) Col_C (Amt)
01-Jan-2009 Japan 1500
02-Jan-2009 Singapore 2000
05-Jan-2009 Singapore 1700
06-Jan-2009 Japan 1000
10-Jan-2009 Korea 1500

Now I want get in Sheet2 just last date & Pmt Amt like as under :
Japan 06-Jan-2009 1000
Singapore 05-Jan-2009 1700

Is it possible ?

Thanks in advance.