Free Microsoft Excel 2013 Quick Reference

Compare Two Worksheets.

I am looking for a VBA Macros to compare 2 worksheet within same workbook. The script should look the data from First List i.e. sheet1 and compare with Second List (updated) i.e. sheet2.

Here are the details. When member run the VBA Macros a message will be displayed to him through inputbox saying that whether he is looking for same records or different records and what are the columns he want to compare. The VBA Macros should look all the three values given below and display complete row in new sheet based on his selection the result will be displayed.

In both conditions i.e. same or difference the script should look for following fields:

Note: These fields are frequently changing the columns. Let say one client put Age in column D another client will put in column G. Want I am trying to tell you here is fields are not fixed. This is the reason i am requesting for inbox above to enter 3 columns i.e. A,G,T to be entered by user.

Sample file is attached for your easy reference.

Post your answer or comment

comments powered by Disqus
Hello Every body
I want to compare two worksheets, when the two worksheets are compared then the resultant data must be shown in worksheet 3 with row number which has been compared and also full row must not be copied to worsheet 3 but some cells of the row must be copied. Sample workbook i have attached with this email. Please solve the problem I need it urjent.

I need help with a macro that compares two worksheets then lists the differences on a third sheet. The data looks at changes in employees each month, and I need to see who has left or been added each month. The unique identifier for each is the E ID#. I would like the third sheet to show who has left between the two months (under “Left” header) and who has been added (under “Added” header).

I have found similar threads on the site but haven’t been able to find one that addresses this exact situation. I have attached an example. Thank you in advance for your help.

hi friends...
how to Compare two worksheets records using VBA in Microsoft Excel and copy the difference into new sheet.
I have tried but the performance is very low and i wanted to improve the performance.(both sheets contain near about 20000 records).Please give me some solution.


Hello -

I've been looking for way I can compare two worksheets cell by cell.

In short, I'm seeking to duplicate the "diff" functionality in Unix.

Also, if the solution is too elaborate than can someone provide a Macro to compare two columns of data and return matching numbers only.

I read the Vlookup solution posted but wanted to do other things with the duplicate values rather than simply altering the appearance of the value.



Is there a way to have Excel compare two worksheets and highlight the
differences (cell by cell) between the two worksheets?

I am trying to compare two worksheets with one having relatable data (see
illustration below-Item). The relatable field has row information which is
pertinent to the 'comparable' field.
Sheet # 2 is the 'master-data' ; using gathered referneced data entered in
sheet # 1 , I am trying to derrive 'matching results and 'unmatching results'
Tried using 'pivot table ; did not receivie likeable results. Is their a
'macros' I could use????
(Please see illustration below)

Sheet # 1 -Entered data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12
001 222 Fruit 12 14 22
001 333 bread 2 12 24

Sheet #2 - Static Data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
002 333 bread 1 10 5
002 222 Fruit 10 12 20
002 111 Egg 10 11 12
002 444 Drink 4 4 4

Would like results of.......
Sheet # 3 - Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12

Sheet # 4 - Not Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 222 Fruit 12 14 22
001 333 bread 2 12 24
002 444 Drink 4 4 4

I need to compare two worksheets and create a new worksheet which is a
difference of the two worksheets

Hi all,

Thank you for a fantastic forum!

I have some problems comparing two worksheets (Sheet 1 and Sheet 2). I would like to compare colum A in Sheet 1 (starting at A2) with colum K in sheet 2 (starting at K2). Before the comparing starts, I need to filter sheet 2 from duplicates depending on colum K. If there is a difference between sheet 1 and sheet 2, I would like to copy colum K and colum N from spreed 2 to the first blank row in spreed 1 colum A resp. colum B

My problem is that I am updating the whole sheet 1 but I would like that sheet 1 only is updated with the diffrences between sheet 1 and sheet 2.

Please see enclosed file for example.

Hi guys,

I have this code to compare two worksheets and copy the rows that are missing in the activesheet from the next sheet.

Sub Old_2_New()
' Assumes:
' -(new)  sheet is selected and "Old" sheet is to the right of the (new) sheet
' - data rows starts on row 3 on both sheets
' - no dummy (non-data) rows below the last data row

 Dim DestinationWS, SourceWS As Worksheet
 Dim bRowExists As Boolean
 Dim iInsertedRows As Integer
 Set DestinationWS = ActiveSheet
 Set SourceWS = ActiveSheet.Next
 Application.ScreenUpdating = False
 LastRowSource = SourceWS.Cells.Find(what:="*", After:=[A1], searchorder:=xlByRows,
 LastRowDestination = DestinationWS.Cells.Find(what:="*", After:=[A1], searchorder:=xlByRows,
 iInsertedRows = 0
 For iRowIndexSource = 3 To LastRowSource
   bRowExists = False
   For iRowIndexDestination = 3 To LastRowDestination
     If DestinationWS.Cells(iRowIndexDestination, 1) = SourceWS.Cells(iRowIndexSource, 1) And _
        DestinationWS.Cells(iRowIndexDestination, 2) = SourceWS.Cells(iRowIndexSource, 2) And _
        DestinationWS.Cells(iRowIndexDestination, 3) = SourceWS.Cells(iRowIndexSource, 3) And _
        DestinationWS.Cells(iRowIndexDestination, 4) = SourceWS.Cells(iRowIndexSource, 4) And _
        DestinationWS.Cells(iRowIndexDestination, 5) = SourceWS.Cells(iRowIndexSource, 5) Then
       bRowExists = True
          Exit For
     End If
   Next iRowIndexDestination
   If Not bRowExists Then 'We found a row in the Source WS that dosn't exist in the destination sheet!
     DestinationWS.Rows(LastRowDestination + 1).Insert (xlShiftDown)
     DestinationWS.Paste (DestinationWS.Rows(LastRowDestination + 5))
     iInsertedRows = iInsertedRows + 1
   End If
 Next iRowIndexSource
 Application.ScreenUpdating = True

 MsgBox iInsertedRows & " unique rows were copied to this sheet"
End Sub

This code works great BUT here is the tricky thing though that I need your help with.

if there is a match, I want to copy the values in columns 10 to 14, 16, 19 to 23, 25, 28 to 32 and 34). So the values in the source worksheet ends up in the right column in the destination worksheet.

i'm attaching a workbook that shows what I mean. All help/ideas is appreciated.

Looking forward to your help with this issue!

My goal is to compare two worksheets and identity data that is not present in both and label each record as such. Essentially, I need to compare transaction numbers from a column of data in worksheet A against a column of data in worksheet B and identify which records are not present in worksheet B.

Can anyone suggest a solution?

Hey guys,

I have used the macro below to great effect to find the differences between two worksheets. However the results are output into a new spreadsheet. I was wondering whether this could be tweaked to overwrite the results onto worksheet 1 instead?

N.B. This code is was beyond my VBA capabilities so take it easy on me!



    Dim r As Long, c As Integer 
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer 
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String 
    Dim rptWB As Workbook, DiffCount As Long 
    Application.ScreenUpdating = False 
    Application.StatusBar = "Creating the report..." 
    Set rptWB = Workbooks.Add 
    Application.DisplayAlerts = False 
    While Worksheets.Count > 1 
    Application.DisplayAlerts = True 
    With ws1.UsedRange 
        lr1 = .Rows.Count 
        lc1 = .Columns.Count 
    End With 
    With ws2.UsedRange 
        lr2 = .Rows.Count 
        lc2 = .Columns.Count 
    End With 
    maxR = lr1 
    maxC = lc1 
    If maxR < lr2 Then maxR = lr2 
    If maxC < lc2 Then maxC = lc2 
    DiffCount = 0 
    For c = 1 To maxC 
        Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..." 
        For r = 1 To maxR 
            cf1 = "" 
            cf2 = "" 
            On Error Resume Next 
            cf1 = ws1.Cells(r, c).FormulaLocal 
            cf2 = ws2.Cells(r, c).FormulaLocal 
            On Error Goto 0 
            If cf1  cf2 Then 
                DiffCount = DiffCount + 1 
                Cells(r, c).Formula = "'" & cf1 & "  " & cf2 
            End If 
        Next r 
    Next c 
    Application.StatusBar = "Formatting the report..." 
    With Range(Cells(1, 1), Cells(maxR, maxC)) 
        .Interior.ColorIndex = 19 
        With .Borders(xlEdgeTop) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeRight) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeLeft) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlEdgeBottom) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        On Error Resume Next 
        With .Borders(xlInsideHorizontal) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        With .Borders(xlInsideVertical) 
            .LineStyle = xlContinuous 
            .Weight = xlHairline 
        End With 
        On Error Goto 0 
    End With 
    Columns("A:IV").ColumnWidth = 20 
    rptWB.Saved = True 
    If DiffCount = 0 Then 
        rptWB.Close False 
    End If 
    Set rptWB = Nothing 
    Application.StatusBar = False 
    Application.ScreenUpdating = True 
    MsgBox DiffCount & " cells contain different formulas!", vbInformation, _ 
    "Compare " & ws1.Name & " with " & ws2.Name 
End Sub 
Sub TestCompareWorksheets() 
     ' compare two different worksheets in the active workbook
    CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2") 
     ' compare two different worksheets in two different workbooks
     'CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Credit to Erlandsen Data Consulting for the original macro.

Firstly, as it's my first time here, congratulations on a great forum ..

I have searched through the forum for answers to my query, but nothing posted quite matches what I am trying to do, which is probably very simple !!

I have two worksheets, containing data formatted in identical rows ..
What I am looking for is a simple method or macro to compare the rows in each worksheet, and output any differences to a third sheet.

I have attached cutdown examples of the files in question ..

I look forward to your replies .. Thanks

Required to compare two workesheets and the result to write in third worlsheet.

Attached an example that should be compared.
Explanation: 1. Worksheet "Original DB"
2. Worksheet "Customer DB"
3. Worksheet "Compare"
All comparisons I make by the car type.
In attached example the "Compare" worksheet shows the required differences.

Any idea for algorythm of VBA code will kindly appreciated.


Suppose there are two worksheets wksh1 & wksh2, values of which are as following."Tracking_ID_435622""Tracking_ID"
I'm comparing the two file names for similarity. According to my requirement, if and contains "Tracking_ID", then the flag is to be set true, false otherwise.
How to go about it?


Hi all,

Recently i was given an excel workbook with two worksheet, both contain the
same data up a 10000 records each, i want to compare the two work sheets and
than copy the missing data to another worksheet using macro, if anyone can
help me with this please i would be grateful.

Message posted via

Scenario (using Excel 2003):
I have two worksheets.
"Sheet1" has 500 entries with the columns Email Address, First Name, Last Name
"Sheet2" has 3000 entries with the columns First Name, Last Name, Buddy Name, IM Platform

I need to compare Sheet1 against Sheet2. For every person in Sheet1 that has a matching First Name and Last Name found in Sheet2 copy all instances of their Buddy Name and corresponding IM Platform into new columns. The trick here is that a person may have multiple buddy names for multiple IM Platforms (i.e. AOL, MSN, Yahoo, etc).

Sample Data:
Sheet1 - | Rob | Smith | Rick | Tyler

Sheet2 -
Rob | Smith | HotDog23 | MSN
Rob | Smith | | AOL
Rob | Smith | yHot | Yahoo
Rick | Tyler | keyz5 | MSN


I need to grab all those entries for each user found in Sheet2 and their Buddy Names and IM Platform

I'm not an Excel programmer so I'm at a loss on how to start this. Any and all help would be greatly appreciated.

Hey there,

I'm not 100% sure this is easy to do in Excel, but I know it's possible in MySQL.

Basically, I'm looking for a macro that can loop through two different workbooks (it's OK if I have to put the worksheet names to compare in the code manually) comparing line for line, not by line number, but by a unique ID that will be living in column A.

Ideally, afterwards this would output which lines deviate, and which column in particular does so.

If need be, this can also be put together into one workbook, and I could copy all the data into two worksheets and we could keep it all within one workbook.

The reason for this is basically comparing data to historic data and seeing if there are differences.

If my description isn't clear please let me know - I'll give whatever details I can.


Hello all - -

I have a workbook that contains two worksheets. The columns are the same between the two, column A is the Employee ID in both worksheets. The other column headings are: Name, address1, address2, city, state, zip, EmgerName, EmgerType, Cell, Address1, Address2, City, State, Zip.

What I'm looking to do is compare WS1 with WS2 and in WS2, what ever is different from WS1, the font color changes to RED. For example let say for employee number 1234, everything is the same expect for EmgerName and Cell, then these fonts are changed to RED in WS2 so I can identify them quickly.

Thanks for taking time to check this post out, you guys (and gals) are truely very helpful!

I'm using MSExcel2003, SP2....

I already read about the technique where you can put two MSExcel
spreadsheets side-by-side and *visually* compare...which is a very "manual"
technique, but...

Will MSExcel "compare" two worksheets and highlight differences for the user
(like you can do in MSWord...where the resulting output highlights
differences in red...great to for comparing differences in different
versions of some text document)?

If not, is there a software app (preferably in WinXP) that would at least
give a "yes" or "no" on whether two files are identical (duplicates?)


Hi all,

Recently i was given an excel workbook with two worksheet, both contain the
same data up a 10000 records each, i want to compare the two work sheets and
than copy the missing data to another worksheet using macro, if anyone can
help me with this please i would be grateful.

Message posted via


I want to create something to compare two worksheets that contain the same data requirement - albeit containing varying data due to new information regularly being added.

Sometimes I need to run comparisons between the data at two different dates and have been using a VLookup to do this. However, as far as I'm aware, a VLookup can only provide a general compare and I want something that will look at all the information, row by row and then say whether there's a general match (i.e., some key fields match but not all) or an exact match (i.e, all the information within a given row matches to a corresponding row in the other worksheet.

To explain what I want, I've attached a file that I've created with random data contained within (hence the A-O column headings). The first worksheet is where the "new" data would go and the second would be the data that i'm comparing against. I'm working on the premise that columns P & Q are already in the worksheet ready to go and that all I would do is copy and paste the data into columns A-O. That said, anything that anyone can suggest to make the tool more intuitive or even "cleaner" would be much appreciated.

My first time on here folks so go easy on me. Been very impressed with this forum so far, from my daily lunchtime peeks...

Thanks in advance for your efforts and assistance

Hi Folks - I have two worksheets that contain loan numbers. The first
worksheet is for the month of May and the second worksheet is for the month
of June. I need a way to compare the two worksheets in the following manner:

* See if there are any loan numbers in May that are not in June and vice

Note: Each worksheet contains different record counts.



I was wondering if it was possible either via VB or formula to compare data against two worksheets on a separate worksheet based on unique data columns that are identical on both worksheets. I am particularly looking to see if it can be done if all of the columns match on both worksheets

For example:

1st worksheet - Raw Data
Column1 Column2 Column3 Column4 Column5 Column6
Dave Dave Don Darrell Jim 1236
Dan Dan Gina Bill Todd 3214
Brandon Brandon Sara Sara Alma 4789

2nd Worksheet - Raw Data
Column1 Column2 Column3 Column4 Column5 Column6
Dave Dave Don Darrell Jim 1236
Dan Dan Gina Eric Todd 3214
Scottie Brandon Sara Sara Alma 4789

3rd worksheet - Results
Column1 Column2 Column3 Column4 Column5 Column6 Column7
Dave Ray Don Darrell Jim 1236 TRUE
Dan Dan Gina Eric Todd 3214 FALSE
Scottie Brandon Sara Sara Alma 4789 FALSE

This would be on a greater scale and my thoughts are of using a VLookup or some formula using SumIf but not sure.

If someone has a suggestion that would be great. It would be cool do to this via VB but anything will help.



I need help from you guys. I have 1000's of records in two different excel sheet. Both the excel sheet have record name (record parameter with minor difference). I want to compare the two excel worksheet data using record name and fetch the required data. At present, I am using Filters to do the process. Because there is 1000's of records, it is very difficult for me to do that. I even searched for "compare excel plugin" using google. But it doesn't solve my purpose.

Pls. find attached the excel sheet. The excel sheet has two worksheet (Data1 & Data2).

Data1 consists of Date, Position, Target, URL, Imp
Data2 consist of Date, Position, URL, Profiles, Ref. by

I have to copy the Profiles & Ref Id details from data2 to data1 by mapping URL. I am using filter, but it is difficult to handle 1000's of data. Is there is any plug in or macro to do this process. Please Help! Thanks in advance


how to compare the two worksheets with similiar Columns using Formulas.

No luck finding an answer? You could always try Google.