-
sir i am not getting any update
-
I mean to say my 2nd post on this thread. :rolleyes:
-
Yes sir i try it but its not working
-
sir also send u a file on your e-mail i'd
pls help me out to get solution
-
First thing:
I have placed a condition in formula which is:
($M$1:$M$35068 <> 0)
because you told me that you only want to see those records which are not 0 in column name "OPEN_INT"
Second thing:
If you need above conditon as well then you need to select more rows and then press F2 and Press CSE(Ctrl + Shift + Enter) as you are using formula only in range W14:W32 you need to resize it for all results.
If both is not the same what you are asking for then why don't you send me the result for all stocks manually which you want me to get using formula may be it will give me better clearity.
And i am talking about the formula in your file which you just sent me.
-
Why don't you use Macro to do this all because using formula it's taking so long to calculate the data as the data is huge. :confused:
-
1 Attachment(s)
Try this and follow the steps:
Select cell R14:R32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),1),"")
Select cell S14:S32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),3),"")
Select cell T14:T32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),4),"")
Select cell U14:U32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),12),"")
Select cell V14:V32 press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068<=$T$11)*($E$1:$E$35068=$S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068<>0),0,""))*ROW($A$1:$A$35068),""),ROW($A$1:$A$35068)),""),13),"")
Select cell W14:W32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068=$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),1),"")
Select cell X14:X32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),3),"")
Select cell Y14:Y32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),4),"")
Select cell Z14:Z32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),12),"")
Select cell AA14:AA32, press F2 and paste below formuls and confirm it with CSE
Code:
=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068>$S$11)*($E$1:$E$35068=$W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068<>0),0,""))*ROW($A$1:$A$35068),""),ROW($A$1:$A$35068)),""),13),"")
Hope this is what you are asking for.
Please make sure you are applying all this formula in the file you have sent me.
Or you can go through the file. i applied all the formulas in the file. I have deleted some data to make it small in size so please paste all your data into the file and then change the formula mode to Automatic.
Attachment 695
