Compare and find added rows RRS feed

  • Question

  • Hey all, 

    I'm looking for a way to compare two worksheets and find inserted or deleted rows.

    the workbook has been saved (so I can not catch with ws_change or so) and there is not tracking.

    the problem here is that also columns might be added/deleted in one or both worksheets. This makes it difficult.

    so basically an algorithme is required for this, I think?

    Any ideas :)



    Monday, May 23, 2016 4:31 PM

All replies

  • Short version: If you can write out the criteria for identifying 'inserted' and 'deleted' in a way that exactly matches the reality of these two sheets, you should then be able to code something to find these specific cases.

    Question: Is it possible for a row or column to be added or deleted from both sheets? In other words, both sheets started with a column headed 'ThisBigThing' and this column was deleted from both sheets. If this can happen, you can't code a solution. Same thing for inserts.

    In order for this to work, you need some referee value you know to be right.

    Monday, May 23, 2016 4:54 PM
  • There is no timestamp for the individual cells so how can you judge which is later (deleted or inserted) or sooner (deleted or inserted)  when you compare?
    As you already have realized, you need to catch the change and either record it for later (in a log file or the like) or act on it in real-time.

    Best regards, George

    Monday, May 23, 2016 5:13 PM
  • George,

    I'd suggest that, best case, the sheets need to be designed with change control in mind, or that Excel may be the wrong tool entirely and this is something that would benefit either from a database-centric design or some kind of document control method so you can at least recover from bad/unexpected user modifications. Excel is a great tool but it's not for everything!


    Monday, May 23, 2016 5:59 PM
  • Hi Arie,

    >>the problem here is that also columns might be added/deleted in one or both worksheets.

    I think this requirement is not reasonable. You could assume such a situation, you have worksheet1 which contains record A and worksheet2 which contains record B, how do you decide record A is inserted into worksheet1 or record A is deleted from worksheet2. It would be helpful if you could share us the logic for comparing two worksheets.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 24, 2016 3:26 AM
  • Re: compare two worksheets and find inserted or deleted rows/columns

    A fairly quick method would be to determine the last row or last column with data on each worksheet.
    That would only tell you if columns or rows had been inserted or deleted.
    If the row/column counts were the same, you still would not know if existing data had been altered.

    You might want to try my commercial excel add-in "XL Professional" (xl97 to xl2010 only)
    It can compare and provide a list of all mismatched rows in two worksheets or do a cell by cell comparison.
    (3 week free trial, no trackers, no cookies, no ads, no registration)
    Download from the Jumpshare file sharing website... (Dropbox)

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Tuesday, May 24, 2016 3:40 PM
  • Jim,

    Counting rows or columns only tells you if the two sheets are different. Unless one of them is 'known good', you can't say anything else about the sheets. In fact, even if the counts are identical, you can't say anything about the sheets if both can vary; you could have cases where rows/columns were removed and added to both resulting in the same size sheet but the data is completely different.

    Unless there is some method to say 'this is the right sheet structure' so you have a reference, this is a problem without a solution (at least as currently stated)

    The problem is actually much worse. Assume you have three columns 'LN', 'FN', 'SSN'. Row 1 contains: 'Smith' 'Betty' '999-111-3333'.

    Delete column 'FN'

    Insert a new column and name it 'FN'

    Enter entirely new data into this column.

    Now what? Same column count, same column name, col was deleted and added but new data.

    This is a problem that cries out for audit trails generated independent of the sheet so there is some way to see what happened, when, and who did it.


    Tuesday, May 24, 2016 3:56 PM
  • Doug,
    Sounds like we agree.
    Jim Cone

    • Edited by James Cone Tuesday, November 1, 2016 1:26 AM
    Tuesday, May 24, 2016 6:16 PM
  • Jim,

    I agree to agree ;-)


    Wednesday, May 25, 2016 4:52 PM