Macro To Compare Two Excel WorkSheets – Highlight Duplicate Entries

Comparing two excel sheets becomes very useful and handy when there are thousand of entries to be compared. Why to waste hours doing this tedious & not so interesting activity? Here is a step by step procedure to compare data in two excel sheets and highlight the duplicate entries. This Macro also updates the operation’s progress in status bar and helps user in estimating the time taken for comparison.

Creating Macro

First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.

Figure 1

Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “Compare” or a name of your choice and click on Create button.

Clicking on Create button will launch the Visual Basic editor as below.

Figure 3

Copy below code and paste it in the editor.

Save this Macro by closing the editor.

Running Macro

Select Sheet1 and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Duplicate entries will be highlighted in Sheet1.

In the above Macro, it is assumed that first row is a header row. Also, worksheet names are considered to be “Sheet1″ and “Sheet2″. To change the worksheet names or customize this Macro further, follow below steps.

Editing Macro

  • Go to Tools->Macro->Macros. Select the Macro “Compare” and click on “Edit” button.
  • This will launch the Visual Basic Editor.
  • Find the string “CompareWorksheets” in the code.
  • In this line change the name of worksheets from “Sheet1” and “Sheet2” to the ones required.
  • Save the Macro.

  1. This was a lifesaver for me! I wasn’t sure just how to write a macro to do this, but I needed to do it for work. I was dreading comparing numerous spreadsheets to test that I new report I created matched the old report.

    Thanks for the great tip!

  2. Thanks for the macro. But I have a problem. I am comparing 2 excel sheets. Your code did highlight and Bold the identical values. But it did skip cells. I thought about a few reasons…The values that the macro failed to highlight are all coming from a drop down box. Does this matter? I cleared the data validation from both sheets but it still fails to highlight those cells..what do you think might be the reason !! I am just a tiny step away from what I need..This will be a life saver for me comparing 330 sheets with their original !

  3. Hi Lavanya,

    Thank you for visiting Vertical Horizons…!!

    We created a sample XLS with drop down items and it worked fine for us – the macro does identify values in drop down menus and it is not skipping any of the cells.

    There could be some other reason. Well, we have sent you the sample XLS that we used to test this macro (with drop down menus) and you may either modify it as per your requirements and send us back. We will try our best to help you out.

  4. Sandy,

    Can you post your macro’s code here? Can you check if the sheets are referred properly?
    That is the only thind I could think of. Best of luck to you!

  5. Admin,

    Thanks for getting back in touch. I worked around the issue that I had. First of all, I have 330 sheets but I have aroung 75 rows in each sheet. I created a macro, that would paste the original that I am trying to compare at the 100th row and do a conditional formatting to highlight all the cells what is not equal to its original that starts in line 100. FOr example If Row 1 and Row 100 are table headers, Row 2 will be compared to Row 101 and all the cell for which the value does not match from Row 2 to Row 101, the cells will be highlighted. I recorded this as a Macro aso all I have to do is run the macro when I open each sheet, and it highlight s the changes for me. LOVE CONDITIONAL FORMATTING !!

  6. Lavanya,

    We are glad that things eventually worked for you. Of course comparing 330 sheets is a big task.

    But honestly we don’t understand why did you need to copy data from original sheet to every other sheet you are trying to validate. Now that we have realized that you are trying to highlight the difference, it seems that you were referring to wrong macro perhaps. You may probably try below macro:

    Let us know if above macro proves to be better for you.

  7. I have a column with 80 names and in the next 2 columns i have description corresponding to that name.

    But now i have another data with same 80 names but the order is shuffled. I want a macro for copy paste the the description corresponding to the name.

    eg: column 1 : a ,b ,c ,d ,e
    column 2 : This is a , This is b, This is c, This is d, This is e

    Now my column changed to b ,c , a , e , d

    Macro should be for arranging like: This is b , This is c, This is a, This is e, This is d

    Hoping for your help . Thanku

  8. Hello ,

    Thanx for ur query ,i have one issue in excel actually we need to compare both sheet by row wise (but above query its mentioned cells wise) i need proper solution mam, For example …I uploaded 1000 records in one sheet and another sheet 750 records we will assume 750 records are duplicate it will appear as colour we will find easily but we need highlighted rows must be save in new sheet is it possible… give the solution it will useful for me …i need to get the particular row …rest of row i need to upload ,no need overlap tats y we neeed solution ….

    example name dept location status
    1 xxx cse uae helll
    2 yyy cse ind skdk
    3 ded it uae dlsd
    4 ght cse uae hddkkd

    i need individual row should be highlighted…no need for first row and last row same dept and locatiob but different name it should appears individual row so pls say solution to find…no need for cells to check….

  9. While working with this macro, what can I do so that each duplicate is only counted ONCE.

    For example, some sheets have more than one of the same exact amount (currency), but each duplicate can only be counted mroe than once if there is another of the same exact amount.


  10. Hi,

    Excellent code i tried this one working perfectly…

    Thanks for giving code with simple format…

    Bhanu Venkat

  11. Hi Admin,

    Thank you for this macro, however I am very new to VBA and require some additional help to get this macro up and running please.
    See what it is, I’ve been looking for a macro that would compare two excel files containing HR data, and either highlight the difference or extract the difference onto a possible 3rd worksheet.

    I’ve got as far as copying and pasted the data I require to compare into the same excel program, on two sepearate tabs for running the macro (sheet1 & Sheet2)

    I’ve then opened the VB editor and pasted the above original posts code in. Then tried to run my button, but get the following error message;

    “Compile error” Expected End Sub – And the top line of the code is highlighted in yellow

    Private Sub CommandButton1_Click() (THIS BIT IS IN YELLOW)
    Sub Compare()

    ‘ Macro1 Macro

    ‘ compare two different worksheets in the active workbook
    CompareWorksheets Worksheets(“Sheet1″), Worksheets(“Sheet2″)
    End Sub

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim diffB As Boolean
    Dim r As Long, c As Integer, m 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

    Any help that anyone can give me is greatly appreciated!!

    Thanking you in advance,

  12. Hi Admin,

    Thank you for your reply.

    I removed the line which was in yellow and re run the macro but nothing happened. So it let me select the button, but nothing appeared in sheet 3, and no error message appeared either.

    I’m now going back and making sure the two files I’m trying to compare are set up the same, is there anything else that you feel I could try?? I do keep the headings on my report within the worksheets, right??

    Is there perhaps something I need to amend within the code in order for the reference to find the files in which I am comparing?

    Have I perhaps copied down to the end of the code correctly, Last line is “Compare ” & ws1.Name & ” with ” & ws2.Name
    End Sub

  13. Hi Andi,

    1. Which button are you talking about. We have not provided implementation for any button in the above code. Please follow the steps provided in the post above to run the macro.
    2. This macro compares two sheets “Sheet 1″ and “Sheet 2″ in the same excel workbook and not two different workbooks. [We have a separate macro for comparing two workbooks]
    3.You can ave the header rows as mentioned in the post above.

    Let us know.


  14. Hi Admin,

    Thank you for this, I think I am getting mixed up between the macro above and code I’ve looked at from other sites.

    I’m going to start again using just the above instructions.

    I can see that this code is to compares two worksheets in the same workbook, but I just cant see where it says to output the differences into Sheet 3?? I can only see that it highlights the differences in bold and a colour.

    the data I am taking this information from runs off two different systems, so one is CSV and other excel, I’ve converted the CSV file and pasted this into sheet 2. Perhaps it would save me a bit of work if I get the code for a macro to compare two files on seperate workbooks??

    Appreciate all the help you are providing.

  15. Okay please dont laugh, but I’m using MS office Excel 2007. I can not see a tools option within this. Instead I have added a ribbon called developer and within this design mode, I have added a button from here in which I have pasted your code.

    Take it this is not correct then??

    • Will I be paid weekly or motnhly? She is soooo much better at fucking than he is!!!! He had a nice cock though The first half was pretty good. Eye contact,kissing,intensity..they seemed to be really enjoying fucking each other..He got lazy in the last half thou.. too much doggy style, boring!! he was sitting on his ass and she was doing most of the work..Cumshot was sooo weak. he shoulda just came on her ass.. An internal shot woulda been really HOTTT!!! Oh well

  16. Hi Andi,

    No issues at all – the steps mentioned here are written wrt Excel 2003.

    For Excel 2007, follow below steps to create a macro-
    1. On the Developer tab in the Code group, click Record Macro.
    2. Give the macro name and click on “Create” button
    3. This should launch vb editor where you can paste above code

    Steps to run this macro-
    1. Again on the Developer tab in the Code group, click Record Macro.
    2. Select the macro that you created from drop down list and click on “Run” button.

    Let us know if this works for you.

    You may also refer tutorials on other sites to create and run an excel macro in excel 2007.


    • Just about all of the things you say hanppes to be supprisingly appropriate and that makes me wonder why I had not looked at this with this light previously. This article really did turn the light on for me as far as this particular issue goes. Nonetheless there is actually one particular factor I am not necessarily too cozy with and while I try to reconcile that with the core theme of your point, permit me observe what the rest of the readers have to point out.Very well done.

  17. Hi Admin,

    I’ve went and watched some tutorials from other sites so have a bit of a better understanding of Macro’s now.

    I’ve opened VBE, within VBA project I’ve then pasted the code under sheet1
    I’ve then selected run from the top menu bar, however from here it just freezes.

    I’ve waited a good 5/10 minutes but programs not responding. So did Ctrl Alt and Delete and closed down excel. The system then trys to recover the file and I can now see these changes;

    Worksheet 1 = The title is now in yellow, but cant see any other change
    Worksheet 2 = Still the same (Which is supposed to stay the same right??)
    Worksheet 3 = This is now populated with the headings but it does not show me the difference between sheet1 and sheet2

    Any help you are able to provide in order to assist me in where I have fallen down within this macro is greatly appreciated.

  18. Hi Andi,

    We have shared an XLS on the email id that you have provided. This XLS is created in Excel 2007 We have created a macro as explained in the post and it works fine. Please have a look.

    What you can do now is to:

    – Copy your data in “Sheet 1″ and “Sheet 2″ in attached XLS and run the macro
    – OR, try to create the macro again and check
    – OR, share your XLS if data is not confidential [even a snippet or dummy data will do]. It will help us to debug the issue.

    We have also posted steps to work with an excel macro in Excel 2007 for your ease, please have a look at below link:

    Let us know if it works for you now.


  19. Hi Admin,

    Thank you for your recent reply, the information you have supplied me with so far, is greatly appreciated.

    I have amended the input slightly and played with your macro and this works fine, so thank you for that. I have saved this as a template and will use this when I require that report. However I think I’m way off track wtih this macro code as it’s not actually going to fit my needs for the main report I need to compare.

    Is there any chance you could help me with creating a macro to suit the following;

    Just like this macro, I require to compare two worksheets within the same workbook however rather then extracting the duplicate entries and showing this on sheet 3, I would require the program to bring back only the difference between the two reports on sheet 3. And if possible highlight the difference on sheet 1 too??

    I have emailed you a copy of the excel file I am trying to get the macro to work in. For the comparison I am trying to carry out, sheets 1 & 2 would contain up to 3,000 lines. Would this cause a problem at all??

    Basically I work in Payroll and our HR department have bought a completely different system from us so i need to run a dump from there system and ours and compare the two on a monthly basis.

    Any help / assistance you are able to provide me is greatly appreciated.

  20. Hi Admin,

    I’ve been searching for VB code that may suit the main comparison report I require to run and have found the below code which is bringing back difference on sheet 3, however it seems to be bringing back more lines in sheet 3 then I have in sheet 1 & 2 together.

    Is there any code I could use, to tailor this slightly and only bring back the information that is missing from sheet 2, compared too sheet1?? Or highlight the actual cell which contains the difference in any of the sheets??

    Code I’m currently using;

    Sub LookForDiscrepancies()
    Dim varS1, varS2
    Dim rngS1 As Range, rngS2 As Range
    Dim c As Range, c1 As Range, c2 As Range
    Dim iRow As Integer, iCol As Integer, i As Integer, iTest As Integer

    Set rngS1 = Intersect(Sheet1.UsedRange, Columns(“A”))
    Set rngS2 = Intersect(Sheet2.UsedRange, Columns(“A”))

    Let iRow = iRow + 2
    With rngS2
    ‘Search for Sheet1 AU IDs on Sheet2
    For Each c1 In rngS1
    On Error Goto 0
    Set c = .Find(what:=c1.Value) ‘Look for match
    If c Is Nothing Then ‘Copy the AU ID to Sheet3
    Sheet3.Cells(iRow, 1) = c1
    Let iRow = iRow + 1
    Else ‘Check if rows are identical
    Let varS1 = Intersect(Sheet1.UsedRange, c1.EntireRow)
    Let varS2 = Intersect(Sheet2.UsedRange, c.EntireRow)
    Let iCol = Intersect(Sheet1.UsedRange, c1.EntireRow).Count
    For i = 1 To iCol
    If Not varS1(1, i) = varS2(1, i) Then Let iTest = iTest + 1
    Next i
    If iTest Then ‘Rows are not identical
    For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    Next i
    Let iTest = 0
    Let iRow = iRow + 1
    End If
    End If
    End With

    Let iRow = iRow + 2
    With rngS1
    ‘Search for Sheet2 AU IDs on Sheet1
    For Each c2 In rngS2
    On Error Goto 0
    Set c = .Find(what:=c2.Value) ‘Look for match
    If c Is Nothing Then ‘Copy the AU ID to Sheet3
    Sheet3.Cells(iRow, 1) = c2
    Let iRow = iRow + 1
    Else ‘Check if rows are identical
    Let varS1 = Intersect(Sheet2.UsedRange, c2.EntireRow)
    Let varS2 = Intersect(Sheet1.UsedRange, c.EntireRow)
    Let iCol = Intersect(Sheet2.UsedRange, c2.EntireRow).Count
    For i = 1 To iCol
    If Not varS1(1, i) = varS2(1, i) Then Let iTest = iTest + 1
    Next i
    If iTest Then ‘Rows are not identical
    For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    Next i
    Let iTest = 0
    Let iRow = iRow + 1
    End If
    End If
    End With
    End Sub

    AGAIN, thank you so much for all the help you have provided so far, it is greatly appreciated.


  21. Hi Admin,

    Me again!! Really sorry to keep bothering you, however need some more assistance pretty please.

    I can now see that the above code brings back the difference from sheet2 using the unique identifier from sheet1. and reports this in sheet3, then does the same thing for the other sheet.

    My question now is, is it possible to amend the code to show the results on two seperate sheets, so sheet 3 & 4?? This way it would be easier to manage as i’m refering to more than 2000 employees information.

    And if that is possible, is it then possible to mark the difference somehow in the actual cell were the difference appears on that line?? so I first thought of highlighting the actual difference in a colour however have since realised that if I do this, then I have no way of filtering on my difference at a later date, as you cant filter on a colour on excel??? or can you??

    Any help you provide is greatly appreciated.


  22. Hi Andi,

    We have got our team to write this macro for you that compare two worksheets, highlight different entries in Sheet 1 and copy them to Sheet 3. We hope that this is what you are looking for. Below is the link for vb code:

    You can easily filter based on colors in excel 2007. All you need to do is to create a normal data filter on columns and then on filter drop down you can see “Filter by color” option.

    Let us know if your problem is finally resolved. :)


  23. Also, you can compare as many entries as you want. Only thing is that it will take sometime but then status bar is updated to show the progress.

    After seeing the excel sheet that your share, we think there is one more macro that you can look at. It compares two sheets and highlight different cells. Thus, the highlighting is at one level low i.e highlighting different cell rather than different rows. What we have observed is that with your data, rows are same with some data missing in some of the cells of that row. So, you can try the below one as well. Please note that it does not copy anything to sheet 3, it only highlight the differences in sheet 2.

    Let us know what solution you finally opted for.


  24. Thanks so much for this!!

    It worked great and on over 5000 Tags with Descriptions and in under 2 minutes too!!!

  25. Hi Admin,

    Just like to say a big thanks, As I have since got a valid macro that I can use as an HR comparison report up and running. This has since been tried and tested and seems to fit my purpose nicely.

    One small tweek required if at all possible, I’ve been looking at code to use for making my report be case insensitive however I’m not too sure where I would slot this code into the above VB coding?

    would this code even work??

    s1 = “abc”
    s2 = “ABC”
    If StrComp(s1, s2, vbTextCompare) = 0 Then

    Any help you are able to give me is greatly appreaciated.
    Kind regards

  26. Hi,

    I feel happy to see the replies and help that ppl are getting here and I also require one.
    I have a master file with many rows and individual excel files each of which needs to be compared to the master file(only few parameters of these individual files need to be compared and if matches, a number on the row of the individual files need to be placed in the master file against the one which matched.Please let me know the code for this.(only floating point numbers and strings to be matched)

  27. Hello, I just hopped over to your web site using StumbleUpon. Not somthing I would usually browse, but I enjoyed your thoughts none the less. Thank you for making something worthy of reading.

  28. Hi,

    Id like a macro that can compare two exel sheets and delete all the common rows in both these sheets..

  29. Hi,

    Id like a macro that can compare two exel sheets and delete all the common rows in both these sheets..

    • Prayers for a smooth scohol year and for your health. Sorry to hear of your relapse. After my first year of homescoholing (last year) and getting sick for several months during it, I learned that things can slide and you can still recover. I’m pretty organized, my problem was more learning how to go with the flow when my plan had to get tossed It’s a lot of work, but I really enjoy homescoholing.I’m still learning a LOT about just how to teach!On a positive, my kids know more about their faith than I did even as an adult (maybe that’s a negative regarding my lack of knowledge!!)

  30. Hey , I have to sheets I have to compare those two sheets at the begining of the day and also to check at start of next day so can any one send macro code for that

  31. Hi,

    Can anyone help me , I have 2 folders with same name csv files and I want them to compare each same name file through loop, and if they are different i want to highlight it in some sort of log ( may be text or excel or whatever),story seems to be simple till above but there is a catch, i want to compare A1 A2 ( cell) and column D of each same name file of folder 1 to 2.
    Pls. help me :(

  32. Hi, I’m trying to get a macro that will compare two specific columns in two sheets from two different workbooks (cG,ws4/wb1 to CA,ws1/wb2).
    and only highlight the duplicates found on ws1/wb1, (I would like to highlight the whole row from where it found the match, not only the cell.)
    Also it would be perfect if it would promt me at the very beginning for which workbook to compare it to.

    The following code I have, does everything exept for highlighting the whole row, it only highlights the cell, and it only finds duplicates that match with the exact row and column within the two books:

    Sub Compare()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Rng As Range
    Dim sBook As String

    If Workbooks.Count < 2 Then
    MsgBox "Error: Only one Workbook is open" & vbCr & "Open a 2nd Workbook and run this macro again."
    Exit Sub
    End If
    Set wb1 = ThisWorkbook
    For Each wb2 In Workbooks
    If wb2.Name wb1.Name Then Exit For

    On Error Resume Next
    Application.DisplayAlerts = False
    sBook = Application.InputBox(Prompt:=”Compare this workbook (” & wb1.Name & “) to…?”, _
    Title:=”Compare to what workbook?”, Default:=wb2.Name, Type:=2)
    If sBook = “False” Then Exit Sub
    If Workbooks(sBook) Is Nothing Then
    MsgBox “Workbook: ” & sBook & ” is not open.”
    GoTo ReDo1
    Set wb2 = Workbooks(sBook)
    End If

    Application.ScreenUpdating = False
    For Each ws1 In wb1.Sheets
    If Not wb2.Sheets(ws1.Name) Is Nothing Then
    Set ws2 = wb2.Sheets(ws1.Name)
    For Each Rng In ws1.UsedRange
    If Rng.Formula = ws2.Range(Rng.Address).Formula Then
    Rng.Interior.ColorIndex = 16
    ws2.Range(Rng.Address).Interior.ColorIndex = 16
    End If
    Next Rng
    If ws1.UsedRange.Rows.Count = ws2.UsedRange.Rows.Count Or _
    ws1.UsedRange.Columns.Count = ws2.UsedRange.Columns.Count Then
    For Each Rng In ws2.UsedRange
    If Rng.Formula = ws1.Range(Rng.Address).Formula Then
    Rng.Interior.ColorIndex = 16
    ws1.Range(Rng.Address).Interior.ColorIndex = 16
    End If
    Next Rng
    End If
    End If
    Next ws1

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

    Sub Clear_Highlights_this_Sheet()
    ActiveSheet.UsedRange. _
    Interior.ColorIndex = xlNone
    End Sub

    Sub Clear_Highlights_All_Sheets()
    Dim sht As Worksheet
    For Each sht In Sheets
    sht.UsedRange.Interior.ColorIndex = xlNone
    End Sub

    Can you please point me out the correct path?
    I would be greately appreaciated, as this is very urgent.
    Thank you in advance!!

  33. Thanks for the Macro. Only problem I am experiencing is that it only highlights one change but pop says there are 24 differences. I don’t see these differences doing a visual compare although there is one additional difference I can definately see, but not highlighted by running the macro. Macro was copied and pasted as is with no changes made.

  34. Hi,
    I used the above macro code given by you
    Sub Compare()

    ‘ Macro1 Macro

    ‘ compare two different worksheets in the active workbook
    CompareWorksheets Worksheets(“Sheet1″), Worksheets(“Sheet2″)
    End Sub

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim diffB As Boolean
    Dim r As Long, c As Integer, m 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…”
    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
    For i = 2 To lr1
    diffB = True
    Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "…"
    For r = 2 To lr2
    cf1 = ""
    cf2 = ""
    On Error Resume Next
    cf1 = ws1.Cells(i, c).FormulaLocal
    cf2 = ws2.Cells(r, c).FormulaLocal
    On Error GoTo 0
    If cf1 = cf2 Then
    diffB = False
    ws1.Cells(i, c).Interior.ColorIndex = 19
    ws1.Cells(i, c).Select
    Selection.Font.Bold = True
    Exit For
    End If
    Next r

    If diffB Then
    DiffCount = DiffCount + 1
    ws1.Cells(i, c).Interior.ColorIndex = 0
    ws1.Cells(i, c).Select
    Selection.Font.Bold = False
    End If
    Next i
    Next c
    Application.StatusBar = "Formatting the report…"
    'Columns("A:IV").ColumnWidth = 10
    m = maxR – DiffCount – 1
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & " cells contain same values!", vbInformation, _
    "Compare " & ws1.Name & " with " & ws2.Name
    End Sub
    For 1000 rows this macro works fine but when i use this for larger data set it hangs.
    Please help me upgrading this macro to work for atleast 50k rows.
    Your help will be a life saver for me.

    Thanks in Advance

  35. Hi Admin,

    I need a little help. Everything looks fine, I’ve tried working with the macro and it’s great. But I need to compare two specific columns, can you help me with this? Also, I need the macro to work for 300K rows.

    Please help. Thanks!

  36. Hello Everyone,

    I tried using the above code for my issue but everytime I tell Excel to run the Macro it tells me “error 9 The index is outside of the valid area” (I translated it from german)… I have no experience in using Macros and don’t know how to solve this problem. Do you have a suggestion? THANK YOU!

    My issue is quite simple:
    I have 2 pricelists in two different worksheets. Now I want to compare the data to see which items in the pricelist are the same…

  37. Hello every body I Just want to know how to put numbering in excel sheet for allotting single number to duplication records viz. I have list of records like this 111111 111111 222222 333333 444444 444444
    I want to numbered them as 1 for both records of 111111
    2 then 3 then 4 will any body help me

  38. Hello, thank you for the awesome code!
    I’m totally new at macros but had to do it for work. It worked perfectly for me.

    Can I ask if there is any way I can check the entire row with multiple columns, instead of the cells one by one?

    • I’ve tried using your code to compare 2 sheets in excel that have over 3000 rows. My excel stops responding and then only highlights some of the duplicates but there are alot left that are not highlighted. Is there any way of fixing this?

      • i need to compare 2 excel sheets and find out whether the data in sheet 1 is covered in sheet 2 by updating the row # / serial # near the corresponding data of sheet 1(base sheet).

        Please help me out in this ASAP.

  39. Hi, really, really useful code, however, it seems to miss some cells out for some reason? Notice that you allowed some people to send over their workbooks for you to view the code and de-bug…is there an e-mail address you can provide so i can do the same?



  40. Hello Admin,
    I involved in data entry. I want my QC to detect errors which may occur in my data sheet. I would like to know the errors. Can you reply to my email.

  41. I have two worksheets with 30,000 records in 60 fields each. This micro just hangs on 4% and doesn’t go ahead. Is this an known issue?

  42. Hi,

    The above code works perfectly however, my boss would like (and so would I, as it would save me hours of boring and pointless work) if we could only compare duplicates over 2 sheets in column a and then separate those duplicates onto sheet3, is this even possible??

    Kind regards,


  43. I perceive alirctes online all the time and some are fairly attractive. Since I’ve written rather a few alirctes myself, and even in print a few books, it could live ready to lend a hand meant for me to get my name absent in attendance by presenting my alirctes. How would I leave regarding putting them online for everyone to access, and could I build cash redistribution them? This may exist a impractical problem excluding, perform public normally look for copyright designed for their online alirctes? Thanks..

  44. Hi this works great! But i’ve a problem, after comparing 2 sheets, i need to copy only selected columns in the sheet 3 . But this copies entire columns. Can you pls help me with that?

  45. Hi All,

    I am new to macro and while doing R&D found this code which is running successfully for one worksheet. Now my question is, if I have to compare two or more than two workbooks or excel files then what will be the changes in the above code?…Can you or anyone help me on this!

    Nitesh Srivastava

  46. Correct me if I am wrong, this macro seems to be comparing column by column for every rows. What if want to compare the entire row as match catch to the next sheet?

    Example as, if I have 100 rows with columns A-D,
    1) A-B is the primary key and based on that check the values for C-D
    2) Highlight in C-D for those not matching
    3) Highlight the entire row if no matching rows returns based on A-B

    Any thought would be great, thanks

  47. t lose when questioning forensic accountants about Due
    Professional Care. Questions related to age should
    be closely examined to be certain that the query is in accordance with ADEA.
    one) Nature – On average, gals devote a lot more time discussing and pondering about matters of family and relationships than males do.

  48. Your article is well written ! However, the design of your website doesnt show up properly under the web browser SEWER NecroPedoSadoMaso 4.1 which is really a shame… hope you can find a way to fix it !

  49. It is regulating their movement if it is running in the wrong direction.
    One of the best ways of accumulating a lot of coins is by buying cheap and selling expensive.
    Pulling just one card of this type can be the key to your Ultimate Team kingdom rising like a phoenix
    or plummeting like our hometown baseball club.

  50. How would I use this code and alter it to check through only certain cells to see if they are the same. For example cell a14, a24, a34 from one of my reports to another report’s cell a14, a24, a34

  51. Hello Admin,

    I am looking for a macro that can compare the values between a column of two workbooks (One contains current activity data and the second is used for archiving historical data) and notify the user of duplicate entries by highlighting the cells in the current activity file that are also in the archive file and a pop-up message box indicating no duplicates have been found if there are none.

    Preferably the macro would be set-up so that it won’t be required to have the archive file opened and somehow it can access it behind the scene through a network file path within the code.

    Thanks for your help!

  52. Just wondering how I could use tweak, the compare Worksheets macro to compare two Workbooks?

    I have Workbooks ‘Period 1′ and ‘Period 2′. In each workbook I have 5 worksheets. I need to compare Worksheet 1 from Period 2 to Worksheet 1 from Period 1 and highlight any Cells that are different.

    Can this be done??

  53. ɦi!,I really like your writing ѕo a lot!
    share we keep up a correspondence extra approximately youг post
    on AOL? I require an expert on this space to unravel my
    problem. Maybe that is you! Having а look fоrward to peeг you.

  54. I was more than happy to discovᥱr this website.
    I want to to thank yoս for ones time just for tɦis fantastic reaɗ!!
    I definitely savߋгed every bit of it and I havе you book marқed
    to see new stսff in your site.

  55. Heү would you mіnd sharing which blog platform you’re using?
    I’m looking to start my own blog soon but Ⅰ’m having a
    difficult time сhߋosing between BlogEngine/Wordpress/B2evolution and Drupal.

    The reason I ask is because your design seems diffеrent then most blogs and I’m looking for something completely unique.
    P.S Soгrү for being off-topіc ƅut I had to ask!

  56. 人気がうなぎのぼりという 顔脱毛の 施術を受けてみたいけど、様々な脱毛サロンのうち どこのサロンで施術してもらうのがいいか 迷っている 、
    そんな決められない あなたに顔脱毛をしている
    人気の脱毛エステ を、 おススメのポイントをしっかり記述する形で お届けします!顔脱毛ができるコースは 、 多くの脱毛エステでみられるという訳ではありませんが、極めてお得に利用できるコース というのもあるようです。 1回で終わりという訳ではありませんので 、初めに 体全体の脱毛を行うコース、つまり全身脱毛コースを選択して、その コースに含まれている 顔脱毛を選択すると、よりお得になる
    場合も結構あるようです。当然、 最初の 顔脱毛で そこの脱毛サロンに通っても大丈夫か をチェックして、数回脱毛をしてもらって、 これなら大丈夫と思えるなら 他の場所もやってもらうのが、失敗が少なくて済む やり方のように思います。顔脱毛の
    施術の経過は 個人差がある ので、 必ずしもそうだとは言い切れませんが、顔脱毛を終えるまでの実施回数は、 個人差 によって、異なるものになります。

  57. I reallʏ like what you guys are uѕually up too.
    This type of clever worҝ аnd coveraɡe! Keep up the terrific works guys I’ve incorpоrated you guys to blogroll.

  58. Hi! I could have swoгn I’ve visited your blog before
    but after going through many ߋf the articles I reaⅼized it’s new to me.
    Anyways, I’m ⅾefinitely deⅼighted I stᥙmbled upon іt
    and I’ll Ƅe book-marking it and checking back often!

  59. Excellent gօods from you, man. I hɑve сonsiⅾer your stuff previous to and you are simply toо excеllent.
    I really like what you’ve obtained right here, reaⅼly like what you are stating and the best
    way through which you asseгt it. You make it entertaining and you stіll cɑre for to stay
    it sensible. I cant wait to learn far more from you.

    This is really a terrific website.

  60. Thɑnks a lot for sharing this with all fоlks you
    really know what you’re talking approximately!
    Bookmarked. Kindly aԁditionally discuss with my website
    =). We can ɦave a link exchange agreement between us

  61. Afteг I initially сommented I sеem to havе сlicked on the -Notify me when new
    comments aгe adԁed- сheckbox and now each time a comment is added I get four emails with the exɑct samе comment.
    There hаs to be an easy method you can remove
    me from that serviсe? Thɑnks!

  62. Thanks for a maгvelous posting! I actually enjoyed reading іt, yߋu can be a great author.
    I will alwɑys bookmark your blog and definitely wilⅼ come back someday.

    I want to encourage yourself to continue your greɑt work, haѵe a nice evening!

  63. For newest infoгmation you have to paу a quick visit world wide weƄ and on world-wide-web I found this sіte as a most excellent web page for ɦottest updates.

  64. I have different BOMs from different vendors in different pages in excel.
    I have to compare all these BOMs and finally want an output as a new excel page showing materials with least price and corresponding vendors. How can I do this? Its very urgent and please help me to do this. Thank you.

  65. 「永久脱毛をやりたい」と思ったのなら、最優先で意思決定しなければいけないのが、サロンで行うかクリニックで行うかということですね。

  66. ハロー。自分は今年で32歳と5カ月になります。そして夏日が多い日になりました。ですからできればいらない毛はをやっておきたいですよね。近年では、全国に脱毛専門のエステサロンがたくさんあります。やりたい場所は、人によりますが、特に多いのはふとももです。私は、人気店のシースリーで脱毛しています。そのおかげで、だいぶムダ毛が少なくなってきました。やはり自分で処理するのとは、効果が凄いです。あとちょっとシースリーに脱毛しにいってムダ毛をなくしたいです。でも、脱毛専門のエステサロンに通ったとしても知りたいのは脱毛にかかるお金です。それについては、従業員に聞けばいいでしょう。あと気になるのが、どれくらい通わないといけないのかです。僕はできれば、3カ月くらいですべて終わってくれると助かりますね。まあ、これからの人はカウンセリングしてみましょう。

  67. はじめまして。私は今月で21歳と7カ月になります。そして夏日が多い日になりました。ですからできればいらない毛は除毛したいですよね。今では、全国に医療クリニックがたくさんあります。やりたい部分は、人によって違いが、特に脱毛したいのはひげです。私は、人気店のシースリーを選びました。そのおかげで、今はムダ毛が少なくなってきました。やはり家で処理するのとは、違います。あとちょっとシースリーに脱毛しにいってムダ毛を減らしたいです。でも、脱毛サロンに通ったとしても気になるのが脱毛にかかる金額です。それについては、シースリーに聞けばいいでしょう。それと気になるのが、いつまで通わないといけないのかです。私はできれば、一年くらいですべて終わってくれるといいですけどね。まあ、今から行く人はカウンセリングしてみましょう。

  68. はじめまして。自分は来週で30歳と3カ月になります。そして夏日が多い日になりました。ですからすぐにでも無駄な毛は脱毛をやっておきたいですよね。最近は、全国に脱毛クリニックがいっくらでもあります。やりたいところは、人それぞれですが、特に人気なのは乳首です。私は、女性に人気のシースリーに通っています。そのおかげで、だんだんとムダ毛が減っています。やはり家で処理するのとは、効果が凄いです。シースリーに脱毛しにいってムダ毛をなくしたいです。でも、脱毛クリニックに通ったとしても知りたいのは脱毛にかかる費用です。それについては、先生に聞けばいいでしょう。他にも気になるのが、どれくらい通わないといけないのかです。私はできれば、半年くらいですべて終わってくれると嬉しいです。まあ、行こうと思っている人は相談してみましょう。

  69. 皮膚自体、身体の中でももっともデリケートな部分ですから、他の部位にくらべて痛みも強いため、少し弱めの施術をお願いするかたも多く、それによって脱毛効果がより遅くなっているという理由もあるかもしれません

  70. I’m really loving the theme/design of your web site. Do you
    ever run into any browser compatibility issues? A handful of my
    blog audience have complained about my blog not working correctly in Explorer but looks
    great in Chrome. Do you have any advice to help fix this issue?

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">