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

How to form an advanced filter query?

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


Joined: 17 Feb 2006
Posts: 14

PostPosted: Sun May 21, 2006 7:51 pm    Post subject: How to form an advanced filter query? Reply with quote

Recently I was using a Calc spreadsheet as a database (because by the time I found out I should have entered it in Base, it was too late) needed to check for the presence of the lowercase letter y as the entire cell contents in any one of about two dozen columns. I tried to use an advanced filter to do this, making a diagonal so that each column contained a y on a separate row. Hence, it was y in the first column OR y in the second column OR etc. I made sure that I selected the right data range and the right filter query range, and that there was a gap between them.

I got an error message saying that the query range didn't contain a valid query. I tried putting it in quotes ("y") and putting an = in front of it ('=y). The help system was no help at all, telling me that the required help page couldn't be found! Mad In vain, I Googled for some documentation, but couldn't find any. Shouldn't such an important and powerful feature be properly documented? Perhaps someone could do so here and tell me what advanced filter query would have matched a y in any of several columns?

(What I ended up doing was an ugly hack that might not have worked in a more complicated situation. I searched and replaced all the y's with 1s, adding a new column, putting a sum function in this column, and auto-filtering for zero results.)

P.S. Is it possible to put text in a monospaced font inline with other text in BBcode?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon May 22, 2006 4:14 am    Post subject: Reply with quote

First: advanced filter is unusable with option "Case Sensitive"
http://www.openoffice.org/issues/show_bug.cgi?id=63748 (still uncommented)
For your specific problem I would use this formula as filter-criterion in an extra column (FIND() is case sensitive without regex-support, whereas SEARCH() could be used case insesitive but with regex):
=ISNUMBER(FIND("y";$A2;1)) * ISNUMBER(FIND("y";$B2;1)) * ISNUMBER(FIND("y";$C2;1))
Last param of FIND is optional and indicates the start-position.
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