remove duplicate rows in calc?

classic Classic list List threaded Threaded
5 messages Options
Dave Stevens Dave Stevens
Reply | Threaded
Open this post in threaded view
|

remove duplicate rows in calc?

There's a bug in the storage layout of some data I'm getting from an
archive that results in duplicate rows in Calc 6.4, adjacent in all the
cases I've seen. Is there a simple way to remove duplicates in this
case? Not all rows are duplicates but as high as 40%.

Dave

--
Affectionate tactile stimulation is a primary need, a need which must
be satisfied if the infant is to develop as a healthy human being.

And what is a healthy human being? One who is able to love, to work, to
play, and to think critically and unprejudicially.

--  Ashley Montagu – Touching, The human significance of the skin. 2e
1978

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: remove duplicate rows in calc?

At 10:16 29/06/2020 -0700, Dave Stevens wrote:
>There's a bug in the storage layout of some data I'm getting from an
>archive that results in duplicate rows in Calc 6.4, adjacent in all
>the cases I've seen. Is there a simple way to remove duplicates in this case?

Try this:
o Select all the material.
o Go to Data | Filter > | Standard Filter... .
o Change "Field name" to "- none -".
o Click Options.
o Tick or untick "Range contains column labels" as necessary.
o Tick "No duplications".
o OK.
o If desired, copy filtered material and paste back or elsewhere as desired.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: remove duplicate rows in calc?

In reply to this post by Dave Stevens
Here's one suggestion that I found:
https://ask.libreoffice.org/en/question/53569/delete-duplicates-in-calc/

Otherwise I guess you have to write a macro.


Kind regards

Johnny Rosenberg

Den mån 29 juni 2020 kl 19:20 skrev Dave Stevens <[hidden email]>:

> There's a bug in the storage layout of some data I'm getting from an
> archive that results in duplicate rows in Calc 6.4, adjacent in all the
> cases I've seen. Is there a simple way to remove duplicates in this
> case? Not all rows are duplicates but as high as 40%.
>
> Dave
>
> --
> Affectionate tactile stimulation is a primary need, a need which must
> be satisfied if the infant is to develop as a healthy human being.
>
> And what is a healthy human being? One who is able to love, to work, to
> play, and to think critically and unprejudicially.
>
> --  Ashley Montagu – Touching, The human significance of the skin. 2e
> 1978
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: remove duplicate rows in calc?

That suggestion looks great, it seems to only manage 8 columns
(conditions), I will have to remember for smaller sheets.
The process at present I use on my data is to sort it in order so
duplicates always appear together. If you need the data back in original
order and don't have a column with a progression add an index column.
Then I tag rows in another column, say with IF(the row below is not the
same)
Then I copy/paste the data to a new sheet (without formulae) and sort it
and delete non-tagged rows.
Then I re-sort back to original order.

Seems cumbersome but doesn't take long.
steve

On 30/06/2020 06:40, Johnny Rosenberg wrote:

> Here's one suggestion that I found:
> https://ask.libreoffice.org/en/question/53569/delete-duplicates-in-calc/
>
> Otherwise I guess you have to write a macro.
>
>
> Kind regards
>
> Johnny Rosenberg
>
> Den mån 29 juni 2020 kl 19:20 skrev Dave Stevens <[hidden email]>:
>
>> There's a bug in the storage layout of some data I'm getting from an
>> archive that results in duplicate rows in Calc 6.4, adjacent in all the
>> cases I've seen. Is there a simple way to remove duplicates in this
>> case? Not all rows are duplicates but as high as 40%.
>>
>> Dave
>>
>> --
>> Affectionate tactile stimulation is a primary need, a need which must
>> be satisfied if the infant is to develop as a healthy human being.
>>
>> And what is a healthy human being? One who is able to love, to work, to
>> play, and to think critically and unprejudicially.
>>
>> --  Ashley Montagu – Touching, The human significance of the skin. 2e
>> 1978
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems?
>> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>>



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: remove duplicate rows in calc?

In reply to this post by Johnny Rosenberg
Is the data a CSV or text file.
Could you pre-process it, i.e.
with awk '!seen[$0]++' file.txt (from
https://stackoverflow.com/questions/1444406/how-to-delete-duplicate-lines-in-a-file-without-sorting-it-in-unix)
or awk '!_[$0]++' file (from
https://www.unix.com/shell-programming-and-scripting/146404-command-remove-duplicate-lines-perl-sed-awk.html)

steve

On 30/06/2020 06:40, Johnny Rosenberg wrote:

> Here's one suggestion that I found:
> https://ask.libreoffice.org/en/question/53569/delete-duplicates-in-calc/
>
> Otherwise I guess you have to write a macro.
>
>
> Kind regards
>
> Johnny Rosenberg
>
> Den mån 29 juni 2020 kl 19:20 skrev Dave Stevens <[hidden email]>:
>
>> There's a bug in the storage layout of some data I'm getting from an
>> archive that results in duplicate rows in Calc 6.4, adjacent in all the
>> cases I've seen. Is there a simple way to remove duplicates in this
>> case? Not all rows are duplicates but as high as 40%.
>>
>> Dave
>>
>> --
>> Affectionate tactile stimulation is a primary need, a need which must
>> be satisfied if the infant is to develop as a healthy human being.
>>
>> And what is a healthy human being? One who is able to love, to work, to
>> play, and to think critically and unprejudicially.
>>
>> --  Ashley Montagu – Touching, The human significance of the skin. 2e
>> 1978
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems?
>> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>>



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy