OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc Advanced Filter

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Thu Oct 05, 2006 12:05 pm    Post subject: Calc Advanced Filter Reply with quote

Hi!
I have problems in using advanced filter in Calc. It works only if I enter (no more than) 8 entries (excluding main label). But when I enter more of them, it doesn't work (it returns the warning: This range does not contain a valid query!) ! What would be a problem? To use filter, then I need to divide the entries into small parts (Cool and then apply filters separately for each part ;(
Thanks for any hint that will help me use Calc without hassle! Greetings, Meta
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Oct 06, 2006 11:40 am    Post subject: Reply with quote

The maximum count of filter-conditions is 8. The maximum count of sort-fields is 3. You may overcome these limits by setting up a datasource. This way your list is accessible through SQL(Structured Query Language).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Fri Oct 06, 2006 12:57 pm    Post subject: Reply with quote

Thanks for answer. Isn't it annoying? In Windows it works flawlessly (sorry...). How can I do it through SQL? Nice day, Meta
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Oct 06, 2006 3:13 pm    Post subject: Reply with quote

slometa wrote:
Thanks for answer. Isn't it annoying? In Windows it works flawlessly (sorry...). How can I do it through SQL? Nice day, Meta

Excel supports more than 8? Well, that's nice to read.
Forget the datasource.
Here's another approach:
If your list has 2 fields like "Date From" and "Date Until" and your criteria match this month's dates by some expressions like "Date From" =">"&TODAY()-DAY(TODAY()) and "Date Until" ="<="&TODAY()
then you may append an extra-field "Span" having a formula like =AND('Date From'>=fromDateCell ; 'Date Until'<=untilDateCell)
and your 2 criteria can be reduced to one "Span" =TRUE.

If this is not practical, you may define 2 refreshable database-ranges and 2 named criteria ranges:
Select entire columns of your list.
Menu:Data>Define...
Name: "List1"
Button "Add"
Name "List2"
Range: "$FreeSheet.$A$1 or some other top-left of free space
More Options>"Insert/Remove Cells"=True
Button "Add"
Button "OK"
Ctrl+F3
Name: "critList1"
Range: <some valid criteria range>
More Options: "Filter"
Button "Add"
Name: "critList2"
Range: <some valid criteria range with additional criteria>
More Options: "Filter"
Button "Add"
Button "OK"
Select List1 or a single cell within
Data>Filter>Advanced
Pick "critList1" from the bottom-left list.
More Options:
Copy Output: "List2"
Persistant: True
Named database range "List2" has the result of the first 8 criteria and is resized(option "insert/remove").

Select List2 or a single cell within
Data>Filter>Advanced
Pick "critList2" from the bottom-left list.

When you have edited the data:
Select List1 or a single cell within
Data>Refresh
Select List2 or a single cell within
Data>Refresh

When the criteria have changed you have to re-call the filter-dialog.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Sat Oct 07, 2006 12:04 am    Post subject: Reply with quote

Hello!
Thank you. I did it until Ctrl+F3, but it doesn't work. I checked 'manually' some comands, but I can't find the applicable. What does it stand for? Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Oct 07, 2006 8:22 am    Post subject: Reply with quote

Edit>Names>Define ...
You can assign names to cells and ranges (among other things). If you check the extra option "Filter" the named range will appear in the advanced filter dialog. Use absolute addresses with criteria ranges: $Filters.$A$1:$D$3 with sheet-name and all the "$"s.
Database ranges are other flavour of named ranges. They "remember" their last sorting, filtering, consolidation rules and -if keeping external data from a datasource- the information about the connection.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Sat Oct 07, 2006 10:39 am    Post subject: Reply with quote

Hi!
I don' know... It doesn't work. I have a table, which I marked as List1, and the criteria range for CritList1. I think I don't need to use critList2, as I only have one type of criteria. I also don't find Persistant option in Filter.
In Advanced filter i do: critList1, and copy to List2 (in empty sheet: $sheet1.$A$1), but the outcome is the same - it can't do... What should go wrong?
And another question: the lenght of criteria range is changing - sometimes I have it longer, sometimes shorter. Should I change it every time in the Name Define?
Sorry for bothering Embarassed
Thank you!
Meta
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Oct 07, 2006 12:22 pm    Post subject: Reply with quote

slometa wrote:
Hi!
I don' know... It doesn't work. I have a table, which I marked as List1, and the criteria range for CritList1. I think I don't need to use critList2, as I only have one type of criteria.
In Advanced filter i do: critList1, and copy to List2 (in empty sheet: $sheet1.$A$1), but the outcome is the same - it can't do... What should go wrong?

My idea was: If 8 criteria are not enough, filter by 8 criteria, copy the result to another place, then filter this copied sub-set by another set of max. 8 criteria.
Well, List1 is a database range (Data>Define...) and CritList1 is a "normal" named range (Insert>Names...), flagged as "Filter". May be there is something wrong with your criteria?
This criteria range selects all values from Field1 having a value between 1 AND 5. Notice the equal labels:
Field1 Field1
>=1 <=5
This criteria range selects all values from Field1 having a value 1 OR 5:
Field1
1
5
Each new line inserts an OR, while empty criteria-cells are ignored:
labels: Field1 Field2 Field3
line1: (Field1=crit1.1 AND Field2=crit1.2 AND Field3crit1.3)
line2:OR(Field1=crit2.1 AND Field2=crit2.2)
slometa wrote:

I also don't find Persistant option in Filter.

Sorry, instead of "Persistant" read "Keep filter criteria".
slometa wrote:

And another question: the lenght of criteria range is changing - sometimes I have it longer, sometimes shorter. Should I change it every time in the Name Define?

When you insert new cells into the existing criteria range, the named reference will be expanded like any other cell reference. SUM(A1:A2) becomes SUM(A1:A3) after insertion of a row, and SUM(named) will expand named=$Sheet.$A$1:$A$2 to named=$Sheet.$A$1:$A$3 likewise.
The same may apply to insertion of cells adjacent to the range if Tools>Options>Calc>General>"Expand references ..." is set. Then you may insert below A1:A3 at row 4 and SUM(A1:A3) becomes SUM(A1:A4).
You can jump to named ranges through the navigator(F5) or picking a name from the name-box left of the formula bar. The name box works with database ranges if you type the desired name into it.
slometa wrote:

Sorry for bothering :oops:
Thank you!
Meta

Your problem is not trivial, and I hope we can find a work-around, using the given Calc-toolset.
You are welcome.

P.S.: The advanced filter has one serious bug, you should be aware of. Everything is capitalized. The program reads cell-criterion "foo" as "FOO" which makes case sensitive filters impossible.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Oct 07, 2006 1:21 pm    Post subject: Reply with quote

My test case:
I want to get all even numbers from a list, having numbers 1 to 32 by using the advanced filter.
New workbook
Database range "List1"=$Sheet1.$$A$1:$A$33
Field1
1
2
3
[...]
32
Blank database range "List2"=$Sheet2.$$A$1 with "Insert/Remove" option
Sheet3:
Field1 Field1
2 <=18
4 20
6 22
8 24
10 26
12 28
14 30
>=16 32

critList1 =$Sheet3.$A$1:$A$9 with "Filter" option
critList2 =$Sheet3.$B$1:$B$9 with "Filter" option

Call the navigator
Goto List1
Data>Filter>Advanced...
Read from List: critList1
Copy to: List2, Keep criteria=True
Goto List2
Field1
2
4
6
[...]
16
17
18
[...]
32
Data>Filter>Advanced...
Read from List: critList2
Field1
2
4
6
8
[...]
28
30
32
Remove 2 from List1
Data>Refresh
Go to List 2
Data>Refresh
Field1
4
6
8
[...]
30
32
Change sheet3:
Field1 Field1
1 <=15
3 17
5 19
7 21
9 23
11 25
13 27
>=15 29

Goto List1
Data>Filter>Advanced...
Just hit OK in order to re-read critList1
Goto List2
Data>Filter>Advanced...
Just hit OK in order to re-read critList2
Field1
3
5
7
9
11
13
15
17
19
21
23
25
27
29
This was just a demo. Normally I would add columns to my list with formulas =ISEVEN(List1) and =ISODD(List1) and then filter by a single filter criterion.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Sun Oct 08, 2006 1:51 am    Post subject: Reply with quote

Hi!
Also, I'm using descriptive data. However, I think it'll be still the easiest and fastest way to keep doing on my 'old' way: to copy-paste parts of criteria and after filtering copying them together in separate list Crying or Very sad .
The suggested procedure it seems to be too complicated and even more time-consuming. I tried it, but I spent too much time on it Confused , sad me...Shocked
Nice day,
Meta
Back to top
View user's profile Send private message
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Sun Oct 08, 2006 7:11 am    Post subject: Reply with quote

would it be easier to sort your list, copy it and then delete unwanted itms in the copy?
_________________
carl
Using OpenOffice.org 2 on XP sp2
Back to top
View user's profile Send private message
slometa
General User
General User


Joined: 21 Mar 2006
Posts: 10

PostPosted: Mon Oct 09, 2006 12:36 am    Post subject: Reply with quote

Hm, I don't think it would be good solution,if I understood you well. My table consists of 505 rows and 11 columns (of descriptive data), and the criteria are around 30.
Greets, Meta
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group