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.