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