Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Compare 2 Excel Worksheets & Identify non-matches

    Hello,

    I am trying to compare the attached worksheets (woody1.xlsx and woody2.xlsx). My task is to combine the 2 worksheets so that we create one master list. However, there are several duplicate values. For example, row 3 on both worksheets are exact matches of each other (case sensitivity can be ignored). The woody2.xlsx worksheet will eventually become the master worksheet. So, what I need to do is figure out which rows from woody1.xlsx are not present in woody2.xlsx.

    I would be very grateful for any help you can provide!

    Thank you!
    Dory
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    So, this is my 1,000th post then.

    From the .xlsx filetypes, I assume that you are using Excel2007 or later.
    In which case, have you not spotted the 'Remove Duplicates' option in the top-panel Ribbon, (Data section)?

    Just append your Woody1.xlsx data below that in Woody2.xlsx (i.e. copy and paste)
    Then, with the cellpointer anywhere in the heading row, click the 'Remove Duplicates' option in the top=panel Ribbon.
    By default, checkboxes for all columns are selected (which is what you want).
    So just click the [OK] button
    Goodbye duplicate rows.

    zeddy

  3. #3
    New Lounger
    Join Date
    Feb 2010
    Location
    Chicago, IL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for the reply! I tried the remove duplicates option and it works fine, but I would like excel to display the duplicates for me before I remove them. Also, using this remove duplicates feature, is it identifying a duplicate based on the entire row?

    Thank you again!
    Dory

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Dory

    So, you just want to display the duplicates because you don't trust Excel then??

    Now, to answer your question, yes, it is identifying duplicates based on the entire row.
    Why? Because ALL the checkboxes for ALL the columns were 'ticked', right?
    What these checkboxes allow you to do is to choose which columns (i.e. data fields) you want to be included in your definition of 'what is a duplicate record'.

    So for example, if you had say, 19 data columns plus one column, say, 'comment', you could check all the 19 but leave the 'comment' column unchecked. This means you completely ignore anything in the unchecked column. So if you had say, three records with all 19 data items the same, but with three different entries in this (example) 'comment' column, then instead of treating these three records as 'unique' (because the 'comments' were different), it treats them as 'the same', and will therefore remove two of the records (i.e. will probably keep the 'first' record it encounters and then delete the other two).

    Now, if you really want to see the 'duplicate' records before deleting them, then we could use formulas and VBA to do that.
    This would involve merging the two data blocks, then sorting the records so that the 'duplicates' are aligned underneath.
    We could then use conditional formats to say, highlight the duplicate record rows in say, yellow.
    And add a [button] to delete the duplicate record rows.

    But, trust me, once you have done a few tests, the Ribbon command to 'Remove duplicate' records is quick and simple!!

    zeddy

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Dory
    You could also use Winmerge [URL="http://winmerge.org/downloads/"], to compare the sheets. Just make sure that you have Plugins-> Automatic Unpacking selected.

Posting Permissions

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