Delete Duplicate Names on Spreadsheets

Discuss the spreadsheet application
Post Reply
susanc
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm

Delete Duplicate Names on Spreadsheets

Post by susanc »

How do I delete duplicate names that are on 25 different
Open Office Spreadsheets. If the tools in Open Office
can't do it, what else can I use to delete duplicate names.
I have Windows 7 Professional.

Thank You!
Susan
Open Office 3.1

Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delete Duplicate Names on Spreadsheets

Post by FJCC »

How are the data organized? By spreadsheet, to you mean 25 tabs in one document or 25 different files? Do you need to delete just the names or are there data associated with the names that must also be deleted? A small example document would be very helpful. After you click Post Reply, look for the Upload Attachment tab just below the box where you type aresponse.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Duplicate Names on Spreadsheets

Post by Villeroy »

Apart from the multiple files, there is always a logical issue when deleting duplicate list entries.
All entries with surname "Smith" do not refer to the same person.
All entries with surname "Smith" and Forename "John" may or may not refer to the same person.

Even if you have a birth date or insurance number and you can assume that duplicate entries refer to the same person, the question remains which one is the list entry to be deleted?

Smith | John | 1954-May-23 | Miami
Smith | John | 1954-May-23 | Jacksonville

This should be considered before you delete anything.
In any case you should create backups of the involved files.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
susanc
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm

Re: Delete Duplicate Names on Spreadsheets

Post by susanc »

FJCC wrote:How are the data organized? By spreadsheet, to you mean 25 tabs in one document or 25 different files? Do you need to delete just the names or are there data associated with the names that must also be deleted? A small example document would be very helpful. After you click Post Reply, look for the Upload Attachment tab just below the box where you type aresponse.
I have 25 different files. I need to delete duplicate names, phone numbers, and
emails. I am using the open office spreadsheet. Thank you and please let
me know if you need anything else. I can't attach a sample because of
privacy but I am using a open office spreadsheet that comes with open
Office. Thank you!
Open Office 3.1

Windows XP
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Delete Duplicate Names on Spreadsheets

Post by RusselB »

Your first (and biggest obstacle), imo, is the fact that your data is split over many different files.
Is there any reason you can't copy and paste the data from each file into a new one, so that you end up with one file containing all of the data?
if you can do that, then you can use Data -> Filter and/or Data -> Sort on the data in the new file, which will allow you to more easily determine which entries you want to delete.
If you can't do that, for some reason (eg: too many rows of data to fit into one sheet), then you could try it with a few files at a time, rather than all of them.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Duplicate Names on Spreadsheets

Post by Villeroy »

Manual copy&paste of 25 sheets in 25 files into one list takes no more than 5 minutes.

Having everything in one list, you can use
menu:Data>Filter>Standard Filter...
<Name field> <Not Empty>
[More Options]
Copy output to A1 of some other sheet
and "Skip duplicates"

This will copy non-duplicate combinations of all selected fields into a new list
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
susanc
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm

Re: Delete Duplicate Names on Spreadsheets

Post by susanc »

Thank you so much.
I will give it a try.
Susan
Open Office 3.1

Windows XP
Post Reply