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

Advanced filter: Questions by a refugee from Excel

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Tue Mar 28, 2006 9:01 am    Post subject: Advanced filter: Questions by a refugee from Excel Reply with quote

I'm a new user with OOo, although quite experienced with MS Office applications.

I've tried to use Calc's advanced filter, but I find it difficult especially in the following matters:

1. I cannot use a defined range for the criteria. Instead, I'm obliged to minimize the Advanced filter dialog and use the scroll bars in order to reach the criteria range and then select it. The Advanced filter dialog does not show any range names in the "Read filter criteria from" dropdown list. This is very cumbersome.

2. How do I define a criterion if I want to select BLANK cells in a column?

3. This is not so important, but is there a way to define my criteria range in a different sheet than the one where my data is listed? I could do this with Excel and I wonder if I could do it with Calc too.

Looking forward to your assistance,
Nigel
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 28, 2006 3:00 pm    Post subject: Reply with quote

1. In the named ranges dialog (Ctrl+F3) show the extra options. You can use four flags with named ranges, one of them is "Filter", the others are related to printing. A range flagged as filter appears in the advanced filter-dialog.
2. I can't get this neither
3. Use a flagged named range.
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Tue Mar 28, 2006 8:44 pm    Post subject: Reply with quote

Thank you for your answers. I didn't know about the extra options in the named ranges. Smile

I wish I could find a solution to my query 2.
The particular column that I want to filter contains dates. Some cells in it are blank. I would like to select the rows with blank cells in the column concerned. This is in addition to some other conditions, and that's why I want to use the Advanced Filter.

Is there another way to use a criterion to select, say, anything in the column that is not a date? Perhaps I might get the filter to work this way.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 29, 2006 12:50 am    Post subject: Reply with quote

In Writer ^$ matches an empty line, but I don't know about some regex matching an empty cell. Sad
Cool A (hidden) column, calculating some aggregated criterion could be an easy solution to filter out some "invalid/incomplete“ rowsets.
Something like:
=NOT(AND(ISNUMBER(myDate);ISTEXT(myArticle);myAmount>0))

Cool Second approach:
It may be helpful to use a filter like:
Date Date
>=2000-1-1 <=2005-12-31
matching numbers representing something related to the years 2000 to 2005.
Another example with calculated criteria referring to a cell A16, specifying a year:
=">="&DATE($A$16;1;1)
="<="&DATE($A$16;12;31)

Cool Third approach (does not work with even more stupid Excel):
A combination of (hidden) extra-column and a “real date filter“ could be:
extra-column: =TEXT(myDate;"YYYY-MM-DD")
the english format-string "YYYY-MM-DD" may vary depending on your regional setting. I prefer ISO-date format because it sorts well in textual context and it works with all languages (AFAIK).
As an example the filter-criterion
^[12][09][0-9]{2}-[0-2][0-9]-[0-3][0-9]$
used with the regex-option matches date-strings from years 1900 to 2999 (a digit 1 or 2, followed by 0 or 9, folowed by 2 any-digits, followed by “-“, ... ).
<>^[12][09][0-9]{2}-[0-2][0-9]-[0-3][0-9]$
finds cells not matching that pattern.

Mad Additional notice on a calc-bug:
Unfortunately the regex-support of Ooo is rather flawy. It's not the real powerful thing like in so many unix-apps and programming languages.
Don't use named character-classes ([:digit:], [:upper:], ...) as they are described in F1-help. Testing the above filter with [:digit:] instead of [0-9] ended in an issue-report:
With advanced filter [:digit:] will be screwed up to [:DIGIT:]. http://www.openoffice.org/issues/show_bug.cgi?id=63748.
There is a file attached, demonstrating the regex-filter and the issue.
Some replacements for named character-classes to be used with advanced filter:
[:digit:] => [0-9]
[:upper:] => [A-Z]
[:lower:] => [a-z]
[:alpha:] => [A-Za-z]
[:alnum:] => [A-Za-z0-9]
The replacements above won't work with non-english letters. [a-z] does not include umlaut „ä“ as [:lower:] does (and clumsy [a-zäöü]).
And another request for enhancment:
Please vote for it!
Menu>Data>Refresh should re-read the criteria from range
http://www.openoffice.org/issues/show_bug.cgi?id=63750
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Wed Mar 29, 2006 8:57 am    Post subject: Reply with quote

Thank you for your detailed reply. I could experiment with your first suggested solution, but I don't know how to use regex matching with Calc. Why or when do you use the "&" character in the filter?

More generally, if I understand correctly, it's not possible to use a formula as part of a filter, the way you do it with Excel. With Excel if you use a formula, then the cell above the criterion should *not* be the same as a field in the source list.

Regarding your second suggestion, it wouldn't work in my case because the column in question contains either a date or a blank cell. If only a blank cell could be said to be the equivalent of a date! As I mentioned, I want to select the rows with blank cells in that column. Sad

By the way, I couldn't vote for the bug you mentioned. Perhaps I'm not allowed by the Administrator to do so.

I don't know what I'll do if I don't find a solution to this problem. I may have to use a database to reord and analyse my data, but I'm not good at using that program, which means a steep learning curve for me. Shocked Sad
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 29, 2006 1:02 pm    Post subject: Reply with quote

Yes, yes, yes! Use databases whenever you can! You get as many sort-columns, lookups, conditions and filters as complex as you are able to define properly.
Quote:
Why or when do you use the "&" character in the filter?

Like in Excel & is the concatenation-operator for strings. You can use "<>"&A1 instead of CONCATENATE("<>";A1). But as you mentioned that is not of interest.
Quote:
More generally, if I understand correctly, it's not possible to use a formula as part of a filter, the way you do it with Excel. With Excel if you use a formula, then the cell above the criterion should *not* be the same as a field in the source list.

You can use almost the same formulas as in Excel (like =“<>“&A1). OOo's regex-support is bad (no match on empty cells and some real bugs), but in general it's much more powerful than ordinary wildchars (and some freaks may calculate regexes by formulas *sigh*). In the end the condition must be meaningful to the application – calculated by a formula or entered as a constant.
BTW: How does Excel match an empty cell? If I remember correctly it works with a formula returning an empty string =““ ?
And what about the extra column having
=ISBLANK(myDate)
or
=ISNUMBER(myDate)
You could include this column in your criteria-range.
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Thu Mar 30, 2006 9:37 am    Post subject: Reply with quote

The idea of using a database is tempting, but I'll have to keep it as a last resort type of solution, given the effort:benefit ratio involved. Smile

I finally used the method of =ISBLANK(myDate) as you suggest. It's a working solution, even though not too elegant, in my opinion, compared to what Excel can do.

I have one more problem concerning the same column: I would also like to filter the data so that I should be able to see a range of dates. If I enter the dates in two reference cells (not inside the data range), say $B$1 and $B$2, what should the criteria be?

I tried ="=AND(>"&$B$1&";<"&$B$2&")", but it doesn't work. It's probably those pesky "&" signs that's causing the problem!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 30, 2006 9:55 am    Post subject: Reply with quote

In a criteria range you put your criteria in one row in order to link them with the AND operator (in Excel too).
Date ---Date --- Name Amount
">"&B2 "<"&B1 Villeroy <100
---------------------Nigel --->10

This criteria mean:
(Date>B2 AND Date<B1 AND Name="Villeroy"AND Amount>100)
OR (Name="Nigel" AND Amount=10)
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Thu Mar 30, 2006 10:23 am    Post subject: Reply with quote

Thank you for the quick reply. What you wrote should have worked, but it doesn't. Sad I tried it with a simplified spreadsheet having a column of various dates. I tried to filter any dates falling between March 30 and April 1, but the result was that no rows were shown after filtering.

Could you validate this test for me, please? I apologize for being so irritating, and I thank you for your patience.

EDIT: Stand by. My test worked, I'll go back to my proper s/sheet and retest!

EDIT 2: Now it works! Oh dear. I don't know why, I must have been doing something wrong - not reading carefully your instructions. Embarassed

Thank you once again for your patience and help in resolving this issue.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 30, 2006 10:43 am    Post subject: Reply with quote

There is a mistake in my previous post.
Date ---Date --- Name Amount
=">"&B2 ="<"&B1 Villeroy <100
---------------------Nigel --->10
I missed the "=".
So the date-criteria should display some text like >2006-03-01 and <2006-04-30.
You may also try =">"&N(B2) which will show the real numeric value of B2 >3877.
I just tried and both methods work unless you have text-values in your date-column.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 30, 2006 10:46 am    Post subject: Reply with quote

[quote="Villeroy"]There is a mistake in my previous post.
Date ---Date --- Name Amount
=">"&B2 ="<"&B1 Villeroy <100
---------------------Nigel --->10
I missed the "=".
So the date-criteria should display some text like >2006-03-01 and <2006-04-30.
You may also try =">"&N(B2) which will show the real numeric value of B2 >3877.
I just tried and both methods work unless your your date-column is made with text-values. In this case the dates are compared in alphabetical order.
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Thu Mar 30, 2006 10:57 am    Post subject: Reply with quote

I picked up the fact that an equals sign was missing and used your suggestion accordingly (see my previous post).

I enter the dates as numbers separated by slashes (/). In the criteria columns the dates are displayed as numbers rather than in date format as you indicate, but that's no real problem for me.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 30, 2006 11:36 am    Post subject: Reply with quote

Mmmh, yes.
Actually I did not do the same thing in my test-sheet. I used plain text >2006-03-01. The formula returnes "=" followed by the real number even without N().
So you've got it right now?
Back to top
View user's profile Send private message
Nigel
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2006
Posts: 140

PostPosted: Thu Mar 30, 2006 11:48 am    Post subject: Reply with quote

Villeroy wrote:
So you've got it right now?

Yes, thank you very much.

I'm beginning to like Calc more and more as I test it. I do miss some features that I used to use with Excel, but I'm optimistic that I can learn to do without them, given enough time. Laughing
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 30, 2006 12:46 pm    Post subject: Reply with quote

There are other features, not in Excel. Database-ranges, named styles, better handling of scenarios, STYLE(), FORMULA(), CURRENT(), regular expressions Twisted Evil (no, just kidding), ...
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