advanced filter with multiple criteria including dates

[Hoss97]

Its a long title, but I'm not sure how to include everything. I am scheduling several work stations and using excel to create the "lists". I am filtering the data to show only what they are able to work on. I am trying to include a date as one of the criterias. (I am using excel 2003) I am trying to filter data to show only orders that has glass being delivered on or before a certain date. In cell A4, I have the the formula =today()+1. My criteria range is $s$1:$ae$3. Within that range I have this formula =G6<=$A$4. All the other criteria's work, except for the date one. (I have no column heading for the date criteria within the critera range) If I only try to filter by the date, it works, however as soon as I try to add the other critera's, it stops filtering by date. Is there a way to get everything to filter as needed?

 

[Aladin Akyurek]

Would you post a small sample including the headers along with the criteria you want to apply?

 

[Hoss97]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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><th>H</th><th>J</th><th>K</th><th>L</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">8/15/2011</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Order No</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Item Number</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Quantity Due</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Status Comment</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Sent to the Powder Coater</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Received from the Powder Coater</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Glass Due</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Stock Glass Code</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Hardware is Assembled</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Springs are Assembled</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Frames are Assembled</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Start Time</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Finish Time</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">44972-1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">RM4129FS2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">CLR BEV GLASS                 </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">21-Jul</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">28-Jul</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">15-Aug</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">4229</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">44944-1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">DB3626FS2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">GRYGLS_BEVELED                </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">          </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">          </td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">15-Aug</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">3629</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Glass Door Glazing</p><br /><br />

 

[Hoss97]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Status Comment</td><td style="text-align: center;;"></td><td style="text-align: center;;">Stock Glass Code</td><td style="text-align: center;;">Start Time</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"><>dswd-4</td><td style="text-align: center;;"><>normandy</td><td style="text-align: center;;"><>afc-100</td><td style="text-align: center;;"><>hdc-150</td><td style="text-align: center;;"><>*cmd</td><td style="text-align: center;;"><>arch-heritage</td><td style="text-align: center;;"><>ba-48-1</td><td style="text-align: center;;"><>directvent</td><td style="text-align: center;;"><>*tpm</td><td style="text-align: center;;"><>*normandy*</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"><>complete</td></tr><tr ><td style="color: #161120;text-align: center;">3</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Glass Door Glazing</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AC3</th><td style="text-align:left">=G6<=$A$4</td></tr></tbody></table></td></tr></table><br />

This is the criteria Range

 

[Aladin Akyurek]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Item Number</td><td style="text-align: center;;">Status Comment</td><td style="text-align: center;;"></td><td style="text-align: center;;">Stock Glass Code</td><td style="text-align: center;;">Start Time</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"><>dswd-4</td><td style="text-align: center;;"><>normandy</td><td style="text-align: center;;"><>afc-100</td><td style="text-align: center;;"><>hdc-150</td><td style="text-align: center;;"><>*cmd</td><td style="text-align: center;;"><>arch-heritage</td><td style="text-align: center;;"><>ba-48-1</td><td style="text-align: center;;"><>directvent</td><td style="text-align: center;;"><>*tpm</td><td style="text-align: center;;"><>*normandy*</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"><>complete</td></tr><tr ><td style="color: #161120;text-align: center;">3</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:10.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Glass Door Glazing</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AC3</th><td style="text-align:left">=G6<=$A$4</td></tr></tbody></table></td></tr></table><br />

This is the criteria Range

S3 downwards:

dswd-4
normandy
afc-100
hdc-150
*cmd
arch-heritage
ba-48-1
directvent
*tpm

Leave S1 empty.
In S2 enter:

=ISNA(LOOKUP(9.99999999999999E+307,SEARCH($S$3:$S$11,B6)))

In T3 enter: normandy

Leave T1 empty.
In T2 enter:

=ISERR(SEARCH($T$3,D6))

Leave U1 empty.
In U2 enter:

=AND(ISNUMBER(G6),G6<=$A$4)

In V1 enter: Start Time
In V2 enter:

<>complete

Try to use S1:V2 as the criteria range.

V1:V2 might need a different set up.

Does this succeed as intended?

 

[Hoss97]

Aladin,

That worked like a charm!! I'm trying to wrap my head around it so that I fully understand it, and then can make it work on some other sheets. On this sheet I need to add one more filter for the stock glass column. If there is a number in that column (for example 4229) or it could have a number like (cv1-3626), I need that row to show even though there might not be a date in the glass due column. This glass would be "in stock"

 

[Aladin Akyurek]

Aladin,

That worked like a charm!! I'm trying to wrap my head around it so that I fully understand it, and then can make it work on some other sheets. On this sheet I need to add one more filter for the stock glass column. If there is a number in that column (for example 4229) or it could have a number like (cv1-3626), I need that row to show even though there might not be a date in the glass due column. This glass would be "in stock"

If all of the other criteria also need to met...

U2 needs modification:

=OR(G6="",AND(ISNUMBER(G6),G6<=$A$4))


And we add another criteria...

Leave W1 empty.
W2:

=H6<>""

 

[Hoss97]

Thank you for continuing to work on this. It worked to a point...it only shows the rows where there is a date in the "Glass Due" (G) column, and a stock glass number in the "Stock Glass Code" (H) column. I need it to show rows where there is a code in column H, and there is no date in column G, as well.


<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3498" width=82><TBODY><TR style="HEIGHT: 91.5pt; mso-height-source: userset" height=122><TD class=xl24 id=td_post_2828846 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 91.5pt; BACKGROUND-COLOR: #ffcc99" width=112 height=122>Glass Due</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" width=82>Stock Glass Code</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3498" width=82><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD class=xl24 id=td_post_2828846 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 23.25pt; mso-ignore: style; mso-pattern: auto none" width=112 height=31 x:str=" "> </TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ccffcc; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" width=82>3933</TD></TR></TBODY></TABLE>

This row is one that gets hidden, but needs to be shown

 

[Aladin Akyurek]

Thank you for continuing to work on this. It worked to a point...it only shows the rows where there is a date in the "Glass Due" (G) column, and a stock glass number in the "Stock Glass Code" (H) column. I need it to show rows where there is a code in column H, and there is no date in column G, as well.


<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=194 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3498" width=82><TBODY><TR style="HEIGHT: 91.5pt; mso-height-source: userset" height=122><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 84pt; HEIGHT: 91.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2828846 class=xl24 height=122 width=112>Glass Due</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffcc99; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=82>Stock Glass Code</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=194 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4778" width=112><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3498" width=82><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND: #ccffcc; HEIGHT: 23.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8; mso-ignore: style; mso-pattern: auto none" id=td_post_2828846 class=xl24 height=31 width=112 x:str=" "></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BACKGROUND: #ccffcc; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8; mso-ignore: style; mso-pattern: auto none" class=xl25 width=82>3933</TD></TR></TBODY></TABLE>

This row is one that gets hidden, but needs to be shown

Do we need criteria other than the ones related to Glass Due and Stock Glass Code?

 

[Hoss97]

Yes, the other critera that we have sorted for has to be met. The date sort worked by itself (with the sorts for the other columns), however, I am trying to add the sort in for the "Stock Glass Code".