Advanced Filter

[gwright]

Hi,

I am trying to build an advanced filter criteria for securities portfolio. Two of the criteria columns, Position (i.e. Long or Short) and Type (i.e. Call, Put, or Option) seem to be working well but I have want the filter to screen only based on a third column, Contract, so that criteria in the first two columns only pick up the row if the criteria is TRUE on a specific contract.

To clarify, the logic looks like this:

BS = "LONG" AND TYPE = "CALL" IF CONTRACT = "SAME"

Its the same part that I don't know how to code. Any ideas?

Thanks.

 

[markmzz]

If I understand correctly what you want, then I think this can help you:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Calc</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Position</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Type</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="FONT-WEIGHT: bold">Contact</TD><TD style="FONT-WEIGHT: bold">Position</TD><TD style="FONT-WEIGHT: bold">Type</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">Long</TD><TD style="TEXT-ALIGN: center">Call</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Name15</TD><TD>Long</TD><TD>Call</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Name12</TD><TD>Long</TD><TD>Call</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Name12</TD><TD>Long</TD><TD>Call</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Name15</TD><TD>Long</TD><TD>Call</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(A3=$A$3:$A$21),--("Long"=$B$3:$B$21),--("Call"=$C$3:$C$21))>1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[Marcelo Branco]

Hi,

Welcome to the forum!

Could you post a small sample of your data?

To do this:
Select a relevant part of your data - 5/10 rows including headers - put borders copy and paste in the forum reply page.

M.