# Thread: Deleting Duplicates

1. In Microsoft Office 2003 I have a spreadsheet in which I need to delete duplicates if column A, B, and C are the same as the another lines A, B, and C then I need it to delete column A thru F of both of those lines. How do I do this?

Thanks

Bonnie
bc41098

2. Please upload a sample file that shows a before and after of what you would like. Your request requires too much guessing as to current structure and expected results.

3. It depends. A macro would not be difficult to write, but if you are cleaning up one spreadsheet, you may be able to do it quickly simply by identifying the duplicates. I like to use the sumproduct formula to identify duplicates.

background: In excel A1=A2 will return either true or false. When used in math, a true converts to 1 and a false converts to 0. In the attached, the product (A2=A3)*(B2=B3)*(C2*c3) = 0 (zero) since at least one is false. If I continue that math, (A2=A5)*(B2=B5)*(C2=C5) I get 1, since all values are true. If I sum all the products, the non duplicates add to 1, the duplicates add to more than one.

Once you identify your duplicates,you can use filters, sorting or other data manipulation to do your deletions.

Note the formula uses anchored ranges (\$) =SUMPRODUCT((A2=\$A\$2:\$A\$7)*(B2=\$B\$2:\$B\$7)*(C2=\$C\$2 :\$C\$7))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•