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

1) put into C1  formula =lower(a1)
2) sort te table on c column
3) put to d1 =0
4) put to d2 = if(c2=c1;1;0) and copy the formula to all rows in d column.
5) select d column, copy CTRL+C and paste special back as unformated text
6) rows with value >0 in d columns have duplicit value with the row above

step 5 is important if you want to sort the table for comparing column b

regards,
Jiri

Dne 12.12.2011 15:39, Hylton Conacher (ZR1HPC) napsal(a):
> 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

--
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