Kirya [.net]

1 | nate

November 24th, 2007 at 3:37 pm

Hell if I know. I’ve always hated spreadsheets. But I am still curious since people come to me for recommendations about stuff time to time.

OoBase would be very nice, if it wasn’t for the complete and total lack of documentation on how to use it. At least that was my experiance last time I tried using it.

How does Gnumeric stand up? I’ve always had the impression that the ‘gnome office’ stuff was superior to the OpenOffice.org stuff, in at least the sort of apps that are present. Gnumeric has certainly been more pleasent for me to use. But, of course, ‘Gnome Office’ can’t hold a candle to ‘OO.org’ in the all-important category of MS Office-compatability.

And, of course, Koffice is standing there in the corner looking all sexy.

2 | Karl Nordström

November 24th, 2007 at 3:48 pm

I don’t use filters very often, but in the case of selecting fields ending in 00 I’d insert an extra column and use the right() function to extract the last two letters. Then one could filter on that column… It’s kind of a workaround and no real solution to the problem…

3 | Julien

November 24th, 2007 at 4:02 pm

Gnumeric is indeed superior to OOoCalc regarding autofilters as it allows me to use simple wildcards like * and ?
However, it suffers from the same issue as for “fill down” feature, and, worse, hidden cells (resulting from a filter) are copied and pasted. Further more, I have never tested the compatibility of the functions with MS Excel (what I notice is that some are stated compatible, other not).

As for the tip consisting i adding a right column, it is indeed a workaround in this very particular case, thanks for sharing it.

Julien

4 | Tim

November 25th, 2007 at 7:19 pm

I just checked on NeoOffice (port of OOo to Mac, v 2.2.x) and regular expressions work well enough for wild card equivalance (via Standard -> More dialog box). I filtered using (Ta)* on some test data, and it behaves as expected, including the checkbox for case sensitivity.

5 | Julien

November 25th, 2007 at 7:33 pm

Indeed, the “Standard -> More dialog box” allows use of regular expressions (the documentation of the box is very clear), but it doesn’t work for me.

I have tried a very simple list on a single column with codes like ‘201′ ‘202′ ‘271′ and have set the criteria to “Column A ends with ‘0?’, which should have displayed the first 2 codes, but have not, for unknown reason. Is there anything I do not understand due to my inexperience with OOoCalc?

6 | Tim Richardson

November 25th, 2007 at 11:32 pm

This regex filter works for me for your example.
= (.+)0.
that is, criteria ‘equals’
set for Regular Expression
and off you go. This is OOoCalc 2.3 in sid [had to leave my wife's Mac ]
I treated the codes as strings (ie the cell values have a leading ‘)

I am a highly advanced Excel user also getting my feet wet in OOo. On balance, I am quite impressed with OOo.

What’s your email? I can send the file.

7 | Julien

November 26th, 2007 at 7:12 pm

Thanks to Tim, I have now understood that the French translation for ‘regular expressions’ (’caractère générique’) was not clear for me.

More over, I have only tried to use wildcards as used in MS Excel, which won’t work in OOoCalc (I haven’t read the documentation as carefully as I had thought, please don’t blame me for this, I will do it *now*).

However, the “fill down” feature does not work the way I want, I am pretty sure this was already reported or might even be an option hidden somewhere…

Thank you again Tim

8 | Tim Richardson

November 27th, 2007 at 11:28 am

I feel bad about a poor answer. A better regex that strictly does what you want is
.*0.$

This really enforces that the second last character must be 0. Works if the contents is text or numeric.
I learnt that the entire cell contents has to match the regex, not some substring.
Excel traditionally offers the very limiting DOS wildcards.

The ? is a special operator in regex language, but it does not mean what it does in DOS wildcards. It means “0 or 1″ of whatever expression it comes after.
In my version of Excel, the “advanced” filter of ?0? only works if the values are strings, not if they are numbers. So chalk one up for OOoCalc. Plus I only get two conditions in Excel.

I don’t see any way to select only visible cells in a range and the Fill Down feature basically ignores filters. This is poor. A workaround is to use find and replace. It has an option to use “Current Selection Only”, which only applies to the visible cells of a selected filtered range.
Apply the filter, and then select the cells (just drag over the whole filtered list). This only selects visible cells, even though you dragged over the whole list.
Now do find and replace.
Turn on regular expressions, and put .* in the “Find” box, which will find every cell in the filter.

In the replace field, put your “fill down” value.

A bit of a hack, but not much more work then fill down.

9 | Julien

November 27th, 2007 at 8:00 pm

Great! I think you should write a how-to on this

As for the ‘Fill down’ feature, though your workaround makes it much easier than filling the cells one by one manually, I guess the behavior should be improved. I will open a feature request on the upstream BTS.

Cheers,
Julien

10 | Julien

November 30th, 2007 at 10:39 pm

Hi,

I have tried the ‘fill down’ feature workaround using regular expressions and “active selection” in the find & replace tool, but the replacements are unfortunately also made in the hidden cells.

I have found a macro (CopyVisibles_Down) which does the job: http://www.ooomacros.org/user.php#188206

Cheers,
Julien