You can find cells with value = x, and optionally replace those with value y. Is there a way to find values not equal to x? Or values > x, or < x, and optionally replace those?
To do this work, you need to use Filter instead of “Find and Replace”
Use the filter to select the cells to be replaced and change them to the required value in any convenient way - paste the desired value from the clipboard or press F2 to enter the edit mode, change the value of one cell and complete Alt-Enter
Update. Try installing this extension - FindByCondition.oxt.
After installing the extension, an additional button should appear on the Find panel (Ctrl+F).
If you have any problems with use, ask again in the comments.
Once they have been filtered, it doesn’t appear to be possible to select them all and perform a “paste special” CTRL+SHFT+V. They can be selected individually and then any paste special actions can be effected - but not as a “whole” group. Do you know if this is intentional or a potential bug?
This is not entirely true. If the filtering results in cells representing a single continuous range, then Paste Special works as usual. Try to sort the original data and only after that apply the filter - the filtered cells will not contain hidden rows and Ctrl+Shift+V will work as usual. In any case, the normal paste with Ctrl+V will replace the filtered values even if they are not in adjacent cells. Why is there a question about Paste Special?
Because Paste Special permits further manipulation ±*/ of the filtered values with a pre-stored value from another cell. Don’t misunderstand, I wasn’t questioning or criticising your suggestion, quite the contrary, I thought it was a well-informed piece of advice and saw the potential for some other things I could contemplate using it for.
I also confirm that it does work as you suggested when the resulting filtered cells are subsequently sorted into a contiguous array. I learned something useful today - Thanks JohnSUN
If I may add one further suggestion - Add a column with a simple index at the beginning of the data array to be filtered & sorted. Then it’s possible to return the data to the original sequence by re-sorting the index if desired.
Thanks John, that was interesting and I learned something that may prove useful.
I work with a massive collection of data. Typically the selection is about 16 columns by 500k lines. The vast majority of cells are all the same value but the cells of interest are different. Filtering it can locate the cells that are different but eliminates everything else so context is lost to a degree. It would be better if LC could just find the cells that are different and shift focus there but apparently that is not possible.
It looks like it will be necessary to use filter to locate the cells of interest, make notes of their location, reset the filter, and manually move to the noted lines.
This could also be made easier with the use of conditional formatting, which is what I was using in Excel 2010, but in LC the conditional formatting is too buggy with a selection this size.
How about filtering the cells you want, filling them with color using direct (non-conditional) formatting, and then removing the filter? When the need for tinted cells disappears, you can simply return the color of the entire column to the default color.
That’s a good idea and I’ll try that but a few impediments occur to me right away.
- There are 16 columns in the selection. Filtering is only possible 8 at a time. So it has to be done at least twice.
- When filtered together, only some of the columns will have different values so highlighting the filtered cells has to be done with manual selections or the filtering must be done 16 times, each column individually.
- Even after the above, it is still necessary to scroll through the entire large file to find the highlighted cells (takes 30-60min at max scroll speed) or make manual notes on the location of the anomalies while filtering.
It would still be much much easier if the FIND feature offered the same kind of =, <>, <, >, etc. that is available in filters. This could also be useful potentially in other situations. Really, if it’s useful to find values that are equal, is it not logical that it would be useful to find values not equal?
I would like to request this feature. How do I do that?
Thank you, sounds perfect.
I’m not sure what it should do exactly but when tried with the sample file it ran for about 20min and apparently stopped with no result. Attempting to close the find window locks and crashes LC.
May I ask you to try the extension on a small sample of data? For example, 16 columns and 500 rows. Just make sure the sheet size was the problem and not the wrong search term. I would also like to see some (this?) sample data. Is this really a BIG SET? Does the worksheet have complex formulas or just data? How long does Calc take to open your file? 20 minutes is very long, it shouldn’t be that way.
The file is about 76MB .CSV, all values with text header, no formulas. Calc takes roughly a minute to open it, an estimate. It is useful sometimes in examining the data to add some calculated fields but usually with smaller files. With the file truncated to 500 lines it works but with some drawbacks:
- The FIND on the toolbar at the top of the screen finds values only in the selection - useful.
- The FIND at the bottom of the screen (extension?) finds values everywhere - not useful.
- The FIND on the toolbar shifts focus to the next found value - useful.
- The CONDITIONAL FIND provides a list of found values - less useful.
The sample data is not secret or private. I would be happy to share but don’t know how to post it in this forum.
Unfortunately, this is NOT a forum, the format of this resource does not imply long discussion and intensive file exchange. I propose to go to communication by mail - my address is indicated in the text of the macro: johnsun at I dot ua. Regarding the shortcomings of the extension - be lenient: this is not even a beta version, this is just a trial pilot macro, an attempt to implement your requests that have been made so far. Agree, that it is desirable to search in the selection or search from the current position down has not been said before - it was implied, but it was not explicitly stated. Now it has been said - now the way for further improvements is known.