Advanced filter question

[yytsunamiyy]

Hi folks,

I have a problem with advanced filter. Using XL 2003, I am trying to construct a criteria range for advanced filter from data in a userform.

The problem is that the userform has a total of 5 listboxes that allow multiselectextended. If I write each condition in its own line as in the following example, I potentially quickly exceed the 65k row-limit for an XL2003 worksheet.

example: User has selected from first listbox 5 entries and from 2nd listbox another 5 entries. The filter should filter as follows:

=AND(OR(= Listbox1 - Criteria 1 to 5), OR(= Listbox2 - Criteria 1 to 5)

This scenario results in the following criteria table if all permutations are spelled out in one cell, one criteria format:

Leistungsfilter


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Listbox 1</TD><TD style="FONT-WEIGHT: bold">Listbox 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>=Criteria 1</TD><TD>=Criteria 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>=Criteria 1</TD><TD>=Criteria 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>=Criteria 1</TD><TD>=Criteria 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>=Criteria 1</TD><TD>=Criteria 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>=Criteria 1</TD><TD>=Criteria 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>=Criteria 2</TD><TD>=Criteria 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>=Criteria 2</TD><TD>=Criteria 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>=Criteria 2</TD><TD>=Criteria 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>=Criteria 2</TD><TD>=Criteria 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>=Criteria 2</TD><TD>=Criteria 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>=Criteria 3</TD><TD>=Criteria 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>=Criteria 3</TD><TD>=Criteria 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>=Criteria 3</TD><TD>=Criteria 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>=Criteria 3</TD><TD>=Criteria 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>=Criteria 3</TD><TD>=Criteria 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>=Criteria 4</TD><TD>=Criteria 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>=Criteria 4</TD><TD>=Criteria 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>=Criteria 4</TD><TD>=Criteria 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>=Criteria 4</TD><TD>=Criteria 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>=Criteria 4</TD><TD>=Criteria 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>=Criteria 5</TD><TD>=Criteria 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>=Criteria 5</TD><TD>=Criteria 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>=Criteria 5</TD><TD>=Criteria 3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>=Criteria 5</TD><TD>=Criteria 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>=Criteria 5</TD><TD>=Criteria 5</TD></TR></TBODY></TABLE>


Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

I realised that I can enter formulas in the criteria range, as long as certain conditions are met (Column header not to equal column header in filterrange, Formula must reference relative to first data row in filterrange ...). That led me to the idea that I could read my listboxes and construct formulas from the that look like that:

=OR([Leistungen.xls]Leistungen!B2="Führungen",[Leistungen.xls]Leistungen!B2="Gastronomie",[Leistungen.xls]Leistungen!B2="Sport",[Leistungen.xls]Leistungen!B2="Übernachtung",[Leistungen.xls]Leistungen!B2="test")

Trouble with that is that formula lengths are limited to 1024 characters. If the user selects many criteria in the listboxes, the formula will become too long to be handled by XL2003.

I tried to circumvent that problem by producing multiple formulas with a max length of <1024, but advanced filter doe snot seem to accept the following criteria range:

Leistungsfilter


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Kat</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">WAHR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">WAHR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">WAHR</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Formeln der Tabelle</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>B2</TD><TD>=OR([Leistungen.xls]Leistungen!B2<"0hne Angabe",[Leistungen.xls]Leistungen!B2>"0hne Angabe",[Leistungen.xls]Leistungen!B2<"Führungen",[Leistungen.xls]Leistungen!B2>"Führungen",[Leistungen.xls]Leistungen!B2<"Gastronomie",[Leistungen.xls]Leistungen!B2>"Gastronomie",[Leistungen.xls]Leistungen!B2<"Sport",[Leistungen.xls]Leistungen!B2>"Sport",[Leistungen.xls]Leistungen!B2<"Übernachtung",[Leistungen.xls]Leistungen!B2>"Übernachtung",[Leistungen.xls]Leistungen!B2<"test",[Leistungen.xls]Leistungen!B2>"test",[Leistungen.xls]Leistungen!B2<"test1",[Leistungen.xls]Leistungen!B2>"test1",[Leistungen.xls]Leistungen!B2<"test2",[Leistungen.xls]Leistungen!B2>"test2",[Leistungen.xls]Leistungen!B2<"test3",[Leistungen.xls]Leistungen!B2>"test3",[Leistungen.xls]Leistungen!B2<"test4",[Leistungen.xls]Leistungen!B2>"test4",[Leistungen.xls]Leistungen!B2<"test5",[Leistungen.xls]Leistungen!B2>"test5",[Leistungen.xls]Leistungen!B2<"test6",[Leistungen.xls]Leistungen!B2>"test6")</TD></TR><TR><TD>B3</TD><TD>=OR([Leistungen.xls]Leistungen!B2<"test7",[Leistungen.xls]Leistungen!B2>"test7",[Leistungen.xls]Leistungen!B2<"test8",[Leistungen.xls]Leistungen!B2>"test8",[Leistungen.xls]Leistungen!B2<"test9",[Leistungen.xls]Leistungen!B2>"test9",[Leistungen.xls]Leistungen!B2<"test10",[Leistungen.xls]Leistungen!B2>"test10",[Leistungen.xls]Leistungen!B2<"test11",[Leistungen.xls]Leistungen!B2>"test11",[Leistungen.xls]Leistungen!B2<"test12",[Leistungen.xls]Leistungen!B2>"test12",[Leistungen.xls]Leistungen!B2<"test13",[Leistungen.xls]Leistungen!B2>"test13",[Leistungen.xls]Leistungen!B2<"test14",[Leistungen.xls]Leistungen!B2>"test14",[Leistungen.xls]Leistungen!B2<"test15",[Leistungen.xls]Leistungen!B2>"test15",[Leistungen.xls]Leistungen!B2<"test16",[Leistungen.xls]Leistungen!B2>"test16",[Leistungen.xls]Leistungen!B2<"test17",[Leistungen.xls]Leistungen!B2>"test17",[Leistungen.xls]Leistungen!B2<"test18",[Leistungen.xls]Leistungen!B2>"test18",[Leistungen.xls]Leistungen!B2<"test19",[Leistungen.xls]Leistungen!B2>"test19")</TD></TR><TR><TD>B4</TD><TD>=OR([Leistungen.xls]Leistungen!B2<"test20",[Leistungen.xls]Leistungen!B2>"test20",[Leistungen.xls]Leistungen!B2<"test21",[Leistungen.xls]Leistungen!B2>"test21",[Leistungen.xls]Leistungen!B2<"test22",[Leistungen.xls]Leistungen!B2>"test22",[Leistungen.xls]Leistungen!B2<"test23",[Leistungen.xls]Leistungen!B2>"test23",[Leistungen.xls]Leistungen!B2<"test24",[Leistungen.xls]Leistungen!B2>"test24",[Leistungen.xls]Leistungen!B2<"test25",[Leistungen.xls]Leistungen!B2>"test25",[Leistungen.xls]Leistungen!B2<"test26",[Leistungen.xls]Leistungen!B2>"test26",[Leistungen.xls]Leistungen!B2<"test27",[Leistungen.xls]Leistungen!B2>"test27",[Leistungen.xls]Leistungen!B2<"test28",[Leistungen.xls]Leistungen!B2>"test28",[Leistungen.xls]Leistungen!B2<"test29",[Leistungen.xls]Leistungen!B2>"test29",[Leistungen.xls]Leistungen!B2<"test30",[Leistungen.xls]Leistungen!B2>"test30",[Leistungen.xls]Leistungen!B2<"test31",[Leistungen.xls]Leistungen!B2>"test31")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

Any pointers on how to solve that issue would be greatly appreciated.

Notes:
I know this should properly be done with a database - but the company I am working for only has XL 03. Database is not an option.

I think it would be better to use an SQL search string to return the desired results, but I:
a) don't knoiw how to write and use SQL - never mind being able to construct the search string via VBA - and
b) believe that that would require MS SQL Server to be installed - another NO NO for the company

As I said - any help is welcome.

PS: Even if you can't contribute to the solution - thank you for reading this long post in the first place.

 

[yytsunamiyy]

Problem solved.

I am simply using the columns to the right of the datafield. I am constructing the necessary formulas with vba, evaluate if all of them calculate with "TRUE" and filter on the evaluation column Y.

Sample for criteria / evaluation Range:

Leistungen

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 98px"><COL style="WIDTH: 202px"><COL style="WIDTH: 182px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">TestSumme</TD><TD style="FONT-WEIGHT: bold">lbxSucheAuswahlKategorie</TD><TD style="FONT-WEIGHT: bold">lbxSucheAuswahPartner</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">0,5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 68px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Formeln der Tabelle</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>Y2</TD><TD>=SUM($Z2:$IV2)/COUNT($Z2:$IV2)</TD></TR><TR><TD>Z2</TD><TD>=--(OR(OR(B2="Übernachtung")))</TD></TR><TR><TD>AA2</TD><TD>=--(OR(OR(D2="Jugendherberge Greifswald")))</TD></TR><TR><TD>Y3</TD><TD>=SUM($Z3:$IV3)/COUNT($Z3:$IV3)</TD></TR><TR><TD>Z3</TD><TD>=--(OR(OR(B3="Übernachtung")))</TD></TR><TR><TD>AA3</TD><TD>=--(OR(OR(D3="Jugendherberge Greifswald")))</TD></TR><TR><TD>Y4</TD><TD>=SUM($Z4:$IV4)/COUNT($Z4:$IV4)</TD></TR><TR><TD>Z4</TD><TD>=--(OR(OR(B4="Übernachtung")))</TD></TR><TR><TD>AA4</TD><TD>=--(OR(OR(D4="Jugendherberge Greifswald")))</TD></TR><TR><TD>Y5</TD><TD>=SUM($Z5:$IV5)/COUNT($Z5:$IV5)</TD></TR><TR><TD>Z5</TD><TD>=--(OR(OR(B5="Übernachtung")))</TD></TR><TR><TD>AA5</TD><TD>=--(OR(OR(D5="Jugendherberge Greifswald")))</TD></TR><TR><TD>Y6</TD><TD>=SUM($Z6:$IV6)/COUNT($Z6:$IV6)</TD></TR><TR><TD>Z6</TD><TD>=--(OR(OR(B6="Übernachtung")))</TD></TR><TR><TD>AA6</TD><TD>=--(OR(OR(D6="Jugendherberge Greifswald")))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

Filtering on Column Y for "=1" will yield the desired result.