Delete Duplicate Names on Spreadsheets

Discuss the spreadsheet application

Delete Duplicate Names on Spreadsheets

Postby susanc » Tue Oct 03, 2017 1:32 am

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
susanc
 
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm

Re: Delete Duplicate Names on Spreadsheets

Postby FJCC » Tue Oct 03, 2017 4:13 am

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6033
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delete Duplicate Names on Spreadsheets

Postby Villeroy » Tue Oct 03, 2017 11:22 am

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24403
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Duplicate Names on Spreadsheets

Postby susanc » Wed Oct 04, 2017 11:58 pm

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
susanc
 
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm

Re: Delete Duplicate Names on Spreadsheets

Postby RusselB » Thu Oct 05, 2017 12:53 am

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.3 and LibreOffice 5.1.3.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4045
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Delete Duplicate Names on Spreadsheets

Postby Villeroy » Thu Oct 05, 2017 8:09 am

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24403
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Duplicate Names on Spreadsheets

Postby susanc » Thu Oct 05, 2017 12:51 pm

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

Windows XP
susanc
 
Posts: 21
Joined: Sat Aug 23, 2014 1:11 pm


Return to Calc

Who is online

Users browsing this forum: RPG and 67 guests