Re: [libreoffice-users] Calc: Identifying duplicate cell values with conditional formatting?

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