Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index


On 2011-12-12 09:39, Hylton Conacher (ZR1HPC) wrote:
Hi,

Using LibreOffice 3.3.1 and am in the process of editing a 68000 row, 10
column file.

There are two main columns that contain the data to be cleaned up with
multiple  instances of duplication i.e. the same text but only the text
case differs between two rows or the text is totally different in column
A row 1 and row 2 but the text in column B rows 1 and 2 is identical i.e.

Col A   Col B   OR      Col A   Col B
a       hx              a       hx
A       hx              a       hx

OR

a       hx              a       hx
a       hy              A       hy

etc for the other combinations

I am doing the alphabetical sort via Col A.

I can use find to search for the duplicate record row once I know what I
am looking for however determining what test is different when the
values in the Col A are the same and vice versa/

On 136000 cells this is a FAIR mission!

I would like to know if there is a conditional formula I could use that
could highlight the differences in one column when cells in the other
column are the same. I am thinking of a formula that says if the cell
contents are the same as any other cell in a range, apply the
conditional format. Of course this conditional would need to be added
onto all 136000 cells. :(

That way I can highlight the 'error' cells and find them easily and
correct them or add a new row of data.

Any pointers would be appreciated for doing this in Calc as an external
database is not available. What elements of the formula can I investigate?

Many thanks
Hylton

Hylton:

If your columns have labels (or you can add some), try using the 'Filter' option in the 'Data' menu. Using the 'AutoFilter' gives the option of filtering the data set by columns. From your example, you can filter Col A for 'a' or 'A', then filter Col B and filter the data for either 'hx' or 'hy'.
This will only show the rows of data that match your filter criteria.

If you use the 'Standard Filter', you can set whether it is case-sensitive.

TomW

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.