Excel 2007 - Compare two sheets and paste the variance in new sheet

2
votes

I have two sheets named sheet1 and sheet2 ; Both have same number of columns which started from A to AI . I need to compare both the sheets if in-case it finds variance in anyone of the individual cell on that time it must to copy the entire row and paste it into new sheet named Sheet3. Kindly reply me as soon as possible.

Many thanks in advance.

2
excelvba
What have you tried? Show us your code so we can improve it. This isn't the sort of place that does your work for you by the way. – Widor
Thanks for your reply. But, I need to write it from the scratch .. Is it possible to compare and paste the variance in new sheet ? – Silent Killer

2 Answers

1
votes

Here is a quick pseudocode sample of how I would go about it:

for each row in worksheet
  for each cell in the row
    if cell on sheet1 <> cell on sheet2
      copy row to sheet3
    end if
  next
next
1
votes

A couple of options

You can download a 30 free trial version of SpreadSheet Advantage which has a

1) column and row alignment function that can be run to set the 2 sheets up in a like for like fashion (to adjust for a deleted or additional row as per Tom's point above).

2) A very quick sheet v sheet compare output that works on formulae, values or both, highlighting only the parts of the cells that are different

or

If the sheets are already perfectly aligned

  • you could use this free compare.xla addin by Myrna Lawson and Bill Manville
  • if you wanted a value comparison only then you could set up a simple formula in A1 of a third sheet to compare the values of A1 in sheets 1 and 2 etc