Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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))
    Attached Files Attached Files

Posting Permissions

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