Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
My table is not sorted and it has multiple entries for a search criteria. I
want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Hi
Regrettably there is no way to tell Vlookup to start from the end and work upwards. If you can accept a VBA solution, the following code will do what you want (no error checking built in) It assumes your codes are in column A and Values in column B Sub FindLast() Dim rng As Range, code As String, lr As Long, value As Double lr = Cells(Rows.Count, "A").End(xlUp).Row code = InputBox("Enter Code to find.") Set rng = Nothing Set rng = Range("A1:A" & lr).Find(what:=code, _ After:=Range("A" & lr), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) value = rng.Offset(0, 1).value MsgBox code & " has a value of " & value End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Select sheet containing the PT's Alt+F8 to bring up Macros Highlight FindLast Run Alternatively you could add a button to your sheet and assign the macro to it. -- Regards Roger Govier "dev" wrote in message ... My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Hi Roger,
Thanks for your help. The code mentioned by you would solve my problem. But I am not able to customize it for my exact requirement. Here is the exact requirement. Following is the sample from a sheet which contains all the transactions: Date Share Qty Price Paid Txn Total Qty Avg. Price 27-Jun SBI 2 1177.95 buy 27-Jun HDIL 5 429.5 buy 27-Jun spicetele 75 71.8 buy 27-Jun Maruti 2 646.4 buy 27-Jun REL 1 906.15 buy 27-Jun REL 1 895 buy 27-Jun REL 1 885 buy 14 1161.54 27-Jun infosys 1 1700 buy 31 1803.72 27-Jun IFCI 25 40.8 buy 30-Jun LT 1 2229.5 buy 1 2229.5 30-Jun TFCI 60 16.7 buy 530 19.37 30-Jun SAIL 15 139.2 buy 15 139.2 30-Jun Siemens 3 391.9 buy 30-Jun Siemens 1 392 buy 33 729.14 30-Jun IFCI 25 38.8 buy 30-Jun REL 2 815 buy 16 1118.22 30-Jun HDIL 5 391.2 buy 30-Jun HFCL 50 14.95 buy 450 17.43 30-Jun JindalSteel 1 1739 buy 30-Jun REL 2 775 buy 18 1080.08 1-Jul RCOM 3 433.25 buy 12 503.65 1-Jul jp 10 137 buy 1110 134.3 1-Jul JindalSteel 1 1639 buy 1-Jul powergrid 15 72.1 buy 355 58.2 1-Jul IFCI 25 32.9 buy 250 44.08 1-Jul SBI 2 1050 buy 21 1391.96 1-Jul HDIL 5 350 buy 30 466.37 1-Jul JindalSteel 2 1640 buy 11 1837.36 1-Jul Maruti 3 580 buy 96 852.04 1-Jul Pantaloon 6 330 buy 50 526.12 I need to prepare a master sheet which contains each share only once alongwith its total quantity and average purchase price. Total Quantity and Average purchase price are determined by the latest entry of the share in the above sheet. So, on the basis of above data, my master sheet will be: Share Total Qty Avg. Price HDIL 30 466.37 HFCL 450 17.43 IFCI 250 44.08 infosys 31 1803.72 JindalSteel 11 1837.36 jp 1110 134.3 LT 1 2229.5 Maruti 96 852.04 Pantaloon 50 526.12 powergrid 355 58.2 RCOM 12 503.65 REL 14 1161.54 REL 16 1118.22 REL 18 1080.08 SAIL 15 139.2 SBI 21 1391.96 Siemens 33 729.14 TFCI 530 19.37 I can maintain first column of the master sheet manually. Can you provide me the code to update other 2 columns. I will make a command button on the master sheet to invoke the code provided by you. This will refresh my data. I will highly appreciate your help. Regards, Dev "Roger Govier" wrote: Hi Regrettably there is no way to tell Vlookup to start from the end and work upwards. If you can accept a VBA solution, the following code will do what you want (no error checking built in) It assumes your codes are in column A and Values in column B Sub FindLast() Dim rng As Range, code As String, lr As Long, value As Double lr = Cells(Rows.Count, "A").End(xlUp).Row code = InputBox("Enter Code to find.") Set rng = Nothing Set rng = Range("A1:A" & lr).Find(what:=code, _ After:=Range("A" & lr), _ LookIn:=xlFormulas, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) value = rng.Offset(0, 1).value MsgBox code & " has a value of " & value End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Select sheet containing the PT's Alt+F8 to bring up Macros Highlight FindLast Run Alternatively you could add a button to your sheet and assign the macro to it. -- Regards Roger Govier "dev" wrote in message ... My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the
value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
This is working on the sample data mentioned by me. But, this is not working
on the actual data. Could you please give brief explaination of the value of each argument which you have passed into the LOOKUP function. Thanks and Regards, Dev "Dave Peterson" wrote: With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Perhaps one formulas tinker to try out as well ..
Sample data posted is assumed within A1:G31 In I2: =IF(B2="","",IF(COUNTIF(B$2:B2,B2)1,"",ROW())) Leave I1 blank In J2: =IF(ROWS($1:1)COUNT(I:I),"",INDEX(B:B,SMALL(I:I,R OWS($1:1)))) In K2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =IF(ISNA(MATCH(MAX(IF(($B$2:$B$31=$J2)*($A$2:$A$31 )*($F$2:$F$31<""),ROW($A$2:$A$31))),IF(($B$2:$B$3 1=$J2)*($A$2:$A$31)*($F$2:$F$31<""),ROW($A$2:$A$3 1)),0)),"",INDEX(F$2:F$31,MATCH(MAX(IF(($B$2:$B$31 =$J2)*($A$2:$A$31)*($F$2:$F$31<""),ROW($A$2:$A$31 ))),IF(($B$2:$B$31=$J2)*($A$2:$A$31)*($F$2:$F$31< ""),ROW($A$2:$A$31)),0))) Copy K2 to L2. Select I2:J2, copy down to L31 Col J returns a uniques list of Shares (from col B), while cols K and L returns the required "from bottom up" results for Total Qty & Avg Price (from cols F & G) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dev" wrote: Here is the exact requirement. Following is the sample from a sheet which contains all the transactions: Date Share Qty Price Paid Txn Total Qty Avg. Price 27-Jun SBI 2 1177.95 buy 27-Jun HDIL 5 429.5 buy 27-Jun spicetele 75 71.8 buy 27-Jun Maruti 2 646.4 buy 27-Jun REL 1 906.15 buy 27-Jun REL 1 895 buy 27-Jun REL 1 885 buy 14 1161.54 27-Jun infosys 1 1700 buy 31 1803.72 27-Jun IFCI 25 40.8 buy 30-Jun LT 1 2229.5 buy 1 2229.5 30-Jun TFCI 60 16.7 buy 530 19.37 30-Jun SAIL 15 139.2 buy 15 139.2 30-Jun Siemens 3 391.9 buy 30-Jun Siemens 1 392 buy 33 729.14 30-Jun IFCI 25 38.8 buy 30-Jun REL 2 815 buy 16 1118.22 30-Jun HDIL 5 391.2 buy 30-Jun HFCL 50 14.95 buy 450 17.43 30-Jun JindalSteel 1 1739 buy 30-Jun REL 2 775 buy 18 1080.08 1-Jul RCOM 3 433.25 buy 12 503.65 1-Jul jp 10 137 buy 1110 134.3 1-Jul JindalSteel 1 1639 buy 1-Jul powergrid 15 72.1 buy 355 58.2 1-Jul IFCI 25 32.9 buy 250 44.08 1-Jul SBI 2 1050 buy 21 1391.96 1-Jul HDIL 5 350 buy 30 466.37 1-Jul JindalSteel 2 1640 buy 11 1837.36 1-Jul Maruti 3 580 buy 96 852.04 1-Jul Pantaloon 6 330 buy 50 526.12 I need to prepare a master sheet which contains each share only once alongwith its total quantity and average purchase price. Total Quantity and Average purchase price are determined by the latest entry of the share in the above sheet. So, on the basis of above data, my master sheet will be: Share Total Qty Avg. Price HDIL 30 466.37 HFCL 450 17.43 IFCI 250 44.08 infosys 31 1803.72 JindalSteel 11 1837.36 jp 1110 134.3 LT 1 2229.5 Maruti 96 852.04 Pantaloon 50 526.12 powergrid 355 58.2 RCOM 12 503.65 REL 14 1161.54 REL 16 1118.22 REL 18 1080.08 SAIL 15 139.2 SBI 21 1391.96 Siemens 33 729.14 TFCI 530 19.37 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Earlier array in K2 seems a little over-roasted
This should suffice: In K2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =IF(ISNA(MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31 <""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2: $F$31<""),ROW($A$2:$A$31)),0)),"",INDEX(F$2:F$31, MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31<""),ROW ($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2:$F$31<"" ),ROW($A$2:$A$31)),0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
(Sheet1!A1:A999=A1)
will return 999 trues and falses. 1/(Sheet1!A1:A999=A1) will provide 999 1's or div/0 errors lookup will not be able to find any 2's in that array so it uses the last largest number it finds that is smaller than or equal to 2. That's the last 1 in the array--the last time that sheet2!a1:a999=a1 =index() uses that number to go into column B and return the value. dev wrote: This is working on the sample data mentioned by me. But, this is not working on the actual data. Could you please give brief explaination of the value of each argument which you have passed into the LOOKUP function. Thanks and Regards, Dev "Dave Peterson" wrote: With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Sorry, there is no =index() in that formula (I was distracted and lost my train
of thought!). It just uses that number to return the value in sheet1!b1:b999 for that row. (ps. change any sheet2's to sheet1's, too.) dev wrote: This is working on the sample data mentioned by me. But, this is not working on the actual data. Could you please give brief explaination of the value of each argument which you have passed into the LOOKUP function. Thanks and Regards, Dev "Dave Peterson" wrote: With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Thanks a lot Max.
It worked for me. Regards, Devender "Max" wrote: Earlier array in K2 seems a little over-roasted This should suffice: In K2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =IF(ISNA(MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31 <""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2: $F$31<""),ROW($A$2:$A$31)),0)),"",INDEX(F$2:F$31, MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31<""),ROW ($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2:$F$31<"" ),ROW($A$2:$A$31)),0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Dave,
Thanks for your help. I took Max's solution since it did not involve VB code. Regards, Dev "Dave Peterson" wrote: Sorry, there is no =index() in that formula (I was distracted and lost my train of thought!). It just uses that number to return the value in sheet1!b1:b999 for that row. (ps. change any sheet2's to sheet1's, too.) dev wrote: This is working on the sample data mentioned by me. But, this is not working on the actual data. Could you please give brief explaination of the value of each argument which you have passed into the LOOKUP function. Thanks and Regards, Dev "Dave Peterson" wrote: With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
My suggestion didn't involve any VBA.
dev wrote: Dave, Thanks for your help. I took Max's solution since it did not involve VB code. Regards, Dev "Dave Peterson" wrote: Sorry, there is no =index() in that formula (I was distracted and lost my train of thought!). It just uses that number to return the value in sheet1!b1:b999 for that row. (ps. change any sheet2's to sheet1's, too.) dev wrote: This is working on the sample data mentioned by me. But, this is not working on the actual data. Could you please give brief explaination of the value of each argument which you have passed into the LOOKUP function. Thanks and Regards, Dev "Dave Peterson" wrote: With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the value for the last IT1 in the list: =LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) dev wrote: My table is not sorted and it has multiple entries for a search criteria. I want last entry to be picked up by vlookup. Is there any way to do that? Or can vlookup search from the bottom of the table? This will also solve my problem. e.g. Item Price it1 it2 32 it3 it1 it4 44 it1 11 it3 20 So, there can be multiple entries for an item but only one entry will have Price (Price will be blank for all other entries of that item). This entry will be the last occurrence of that item in the table. I want price of that occurrence. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching specific record using VLOOKUP function.
Welcome. Do press the "Yes" button below, won't you?
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dev" wrote: Thanks a lot Max. It worked for me. Regards, Devender |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for specific text - how to | Excel Worksheet Functions | |||
Searching for case specific data | Excel Discussion (Misc queries) | |||
Searching two columns against a specific criteria | Excel Worksheet Functions | |||
Searching for the presence of specific words | Excel Worksheet Functions | |||
searching for specific text | Excel Discussion (Misc queries) |