| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
