View Question
Q: De-Duping Records in Excel ( Answered ,   0 Comments )
 Question
 Subject: De-Duping Records in Excel Category: Computers > Software Asked by: cecil69-ga List Price: \$20.00 Posted: 19 Oct 2002 11:03 PDT Expires: 18 Nov 2002 10:03 PST Question ID: 85184
 ```How can one identify and remove duplicate records in Excel without using another application? For example, we have 2 files we will merge into one file. Both files have 3 fields with identical information, 1 field with different information. We want to identify those records with the identical 3 fields and delete them automatically. We need to do this without exporting the data into Access or any other application. I believe the answer exists in the Advanced Filter function, but I can't figure out what to use for the criteria and data ranges. Thanks.```
 Answer
 Subject: Re: De-Duping Records in Excel Answered By: blinkwilliams-ga on 19 Oct 2002 12:08 PDT Rated:
 ```Hello and thanks for the question. Here is a quick and easy way to dedupe records in Excel. 1. Make sure that the records are sorted so that identical records follow one another. Sort ascending by the fields that are repeated. The "sort" command can be found under "Data" on the main menu bar. For example, if field1 contains repeating information then sort by field1 ascending. Afterwards all the records with identical values for that field will be grouped together something like this: apple apple apple orange orange pear pear banana banana 2. Let us assume that your first record is in row1. Go to the first empty column of your first record. I will call this column 'field2'. In this cell (record1 x field2) type the number 1. Now go to the cell directly below it (record2 x field2) and type the following formula: =IF(A2=A1,0,1). The cell references "A1" and "A2" will have to be modified to fit your spreadsheet but basically you want to compare the field1 value in row2 to the field1 value in row1. The formula says: if they are identical return a 0, otherwise return a 1. Once you have entered this formula copy it down for all records. Since it is a formula, you only need to type it once and then copy it down (either by dragging the cell down or by double-clicking the box in the lower right hand corner of the cell). After you have completed this, your spreadsheet should look something like this: apple 1 apple 0 apple 0 orange 1 orange 0 pear 1 pear 0 banana 1 banana 0 Notice that all but the first of record has a field2 value of 0. 3. Now select the column in which you entered the formula. Select 'Copy' on the 'Edit' menu. Then select 'Edit'-->'paste special'. Select 'values' and click ok. This step is important so that the formulas don't update after step 4. 4. Now all you have to do is sort your spreadsheet by field2 descending, the field in which you entered a formula. This will place all the repeaters at the bottom of the spreadsheet. In the example I am using, your results will look like this: apple 1 orange 1 pear 1 banana 1 apple 0 apple 0 orange 0 pear 0 banana 0 You can then delete all the records with a value of 0 for field2. Now you have a deduped list of records: apple 1 orange 1 pear 1 banana 1 It is always a good idea to make a backup version of the original file before you do any sorting or deleting. I hope that helps. Please ask for additional clarification if needed. Best of luck, -blinkwilliams-ga```
 cecil69-ga rated this answer: ```I haven't personally used it, but my co-worker has and says it's good. He's shocked he didn't think of it, but that's what Google Answers is for, right?```

 Comments
 There are no comments at this time.
 Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service. If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for

 Google Home - Answers FAQ - Terms of Service - Privacy Policy