0

HI, I am in need of a program that you can take a spreadsheet file and find all the duplicates with out haveing to go thru each entry. Is there such a thing out there or any way to make up something like that? :?:

6
Contributors
7
Replies
10
Views
12 Years
Discussion Span
Last Post by allancass
0

Hi everyone,

What kind of spread sheet file. Microsoft Excel or something you built yourself custom. What language did you use, C/C++, Java, etc??

Richard West

0

Hi everyone,

What kind of spread sheet file. Microsoft Excel or something you built yourself custom. What language did you use, C/C++, Java, etc??

Richard West

Hello,
I use Microsoft Excel...I know how to check it individually but is there a way or program that will go through and check a whole list or file for duplicates. I am not sure what kind of language is used? sorry. Thanks for your reply.

Kind regards,

datalinx1

0

Hi everyone,

I know how to check it individually

You can actually do that by yourself. Do you use a program written by yourself??

I am actually very interested to know what is the stream format used for a Microsoft Excel file.

Anyways you can use C/C++, Java, Pascal for you task. If you can already check individual values is all you need to do is to create something like the Java ArrayList class. Before you add a value to that array check it with the other values of the array, if there is a duplicate, discard it if not add it to the array. That's it

Please have a format in your array so you know that this is a new row or colomn such as adding the string "column" or "row" so that you do not confuse yourself.

I hope that helps you

Yours Sincerely

Richard West

ps. Please answer the thread at the bootom of my signature

0

You can do this with a simple bit of spreadsheet manipulation. Just sort by whatever column you're checking for duplicates. Then, next to that column, insert a new blank one. Suppose that column you've sorted by is D, and the blank column is C. Suppose the data starts at the fifth row, or D5 (just to pick a number). Then in the blank column, in position C6, just type in the formula, =D5=D6. This will evaluate to TRUE if D6 is a duplicate of D5..

Then, with square C6 selected, click the bottom right corner of the bold rectangle (surrounding C6) and drag the selection down the entire column. The formula will copy itself, instead with =D6=D7, then =D7=D8, and so on.... You will see 'TRUE' wherever there is a duplicate, and you could find them quickly via Edit > Find....

If your definition of "duplicate" involves more than one equal column, you can make a trivial modification to your formula, something like =D6=D7 and E6=E7, or whatever syntax Excel uses.

You could also save the spreadsheet as a CSV file (comma separated values), write a CSV parser in Perl (or C++, but if you're familiar with Perl or Python or Ruby or ...., life is much easier), and search for duplicates in a small program. But it won't be as easy. I just had to do this exact thing at work (parse CSV files, find duplicates, but a bit trickier), and if you're willing to wait until Monday, I could send you a simple CSV-parsing Perl module (which is not guaranteed to work on all inputs, and would only be useful if you... knew Perl).

However, I recommend using the features of Excel.

0

You can try using auto filter in Excel and filter the columns in the excelsheet.
The ones that are equel are sequential under eachother.

0

This is the easiest, quickest solution for duplicates ever! I used this with a spreadsheet with over 8,000 rows and, unlike using a macro, a formula does not care how many records it is applied to. I am a Data Assessment Coordinator for an elementary school district and end up with spreadsheets with thousands of students and hundreds of duplicates. I have created a cheat sheet with pictures for this and sent it out to others in my district who have had this problem. I have the formula set up to keep the first record found or the last, depending on the needs. As I always say when totally happy: My life is now complete! Thank you for the solution to the duplicate records issue.

You can do this with a simple bit of spreadsheet manipulation. Just sort by whatever column you're checking for duplicates. Then, next to that column, insert a new blank one. Suppose that column you've sorted by is D, and the blank column is C. Suppose the data starts at the fifth row, or D5 (just to pick a number). Then in the blank column, in position C6, just type in the formula, =D5=D6. This will evaluate to TRUE if D6 is a duplicate of D5..

Then, with square C6 selected, click the bottom right corner of the bold rectangle (surrounding C6) and drag the selection down the entire column. The formula will copy itself, instead with =D6=D7, then =D7=D8, and so on.... You will see 'TRUE' wherever there is a duplicate, and you could find them quickly via Edit > Find....

If your definition of "duplicate" involves more than one equal column, you can make a trivial modification to your formula, something like =D6=D7 and E6=E7, or whatever syntax Excel uses.

You could also save the spreadsheet as a CSV file (comma separated values), write a CSV parser in Perl (or C++, but if you're familiar with Perl or Python or Ruby or ...., life is much easier), and search for duplicates in a small program. But it won't be as easy. I just had to do this exact thing at work (parse CSV files, find duplicates, but a bit trickier), and if you're willing to wait until Monday, I could send you a simple CSV-parsing Perl module (which is not guaranteed to work on all inputs, and would only be useful if you... knew Perl).

However, I recommend using the features of Excel.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.