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

On 12/12/2011 10:33 AM, David S. Crampton wrote:
> Hylton,
>
> I can offer a few general considerations and thoughts.  Your problem 
> statement appears to contain two objectives:
>
> a) to make consistent the case of all the elements; all Upper Case; 
> all Lower Case; whatever.  It seems the case makes no difference to 
> the meaning of the record. The case is just an artifact of the typing 
> style of the data input person(s).  In this situation, a mass 
> application of =UPPER or =LOWER and placing the result in a new sheet 
> would suffice.
>
> I think no amount of special formatting will reliably catch your eye 
> to eliminate manually all the instances of differing case.
>
> b) the deduplication. Once the case issue is resolved you will have 
> rows with all elements exactly equal.  This deduplication should be 
> done by exporting the 68000 x 10 sheet to a database and running a 
> deduplication query.  Again any of the visual tricks to identify 
> duplicate rows will be unreliable and you will be guaranteed to miss 
> at least a few.
>
> At this point my advise bogs down. I have past experience with the M$ 
> Access product; recent versions of Access have a pre-written 
> deduplication query. It is not available to me right now because I 
> have left my workplace and don't have an installation of that recent 
> version of M$ Suite.
>
> Use caution in the deduplication process; make lots of backups. It is 
> a delete-type query and data will be lost! Hopefully only the 
> duplicates but you never know.
>
> I hope this vague hand waving is of some help to you,
If the data is in database without removing duplicates you can use the 
following query

SELECT DISTINCT (fields to be used)
FROM database
WHERE any search criteria - can be omitted

Depending on the database you can export the results as csv or txt or 
possibly directly into a spreadsheet. I tend to use MySQL/MariaDB so I 
export as csv.

--
Jay Lozier
jslozier@gmail.com


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