Find Duplicates in Multiple Worksheets

Aug 06th 2011, Category: Macros, Tags: , ,

The ExcelHelpDesk Support team received the following request for help

Problem – Duplicate Names in multiple Worksheets

What is the best way to find duplicates in a workbook with over 40 worksheets? This workbook has over 5,000 names separated into different groups (worksheets). We use this list as a basis for invitation lists. We usually have over 100 names duplicated and are trying to find the best way to highlight the duplicates – we have tried many different methods in the past, but none that finds all the dupes. Please help…

For this problem we received a copy of the workbook from the subscriber. We then developed a specific macro that went through each of the worksheets and determined if any names included were duplicated. Find below a screen shot of the existing lists and worksheets. Then the code that was developed to find the duplicates.

You can use the same code in your own workbook if you have a similar problem. Let us know if you need help in applying the sample code to your situation.

Example of Names and Worksheets with Duplicates

See below an example of the Names provided for Duplicate comparison.

and a screenshot of the Worksheets provided in the sample

Step 1 – Determine approach to find Duplicates

A couple of approaches were possible to find these duplicates, the first involved transferring all names and details to a single worksheet and then re-sorting the list. This would then identify the duplicates by repeated entries close together. The second approach and then one we decided to implement for this post involved the creation of a new worksheet called the “Duplicate Report” on this worksheet each name is included and if a duplicate entry is found a count is increased and the worksheet on which it was found is added to the list of worksheets for that name.

This method allowed for the extension of the workbook and re-use in future years.

Step 2 – VBA Code to search for and report Duplicates

Now here is the VBA Code developed to search for and create the duplicate report. This can be re-used for similar applications in other workbooks. The code is documented to show how each of the key functions are implemented and how the result is dervied.

Sub FindDuplicates()
'This routine will go through each worksheet in the workbook and if
'the worksheet contains names in the expected format, those names will be compared
'to the existing list, if the name is not found it will be included to the list
'if it is found the count will be increased and the worksheet name added to a list of
'worksheet names for that name

Dim myOutputRange                       As Range
Dim myRange                             As Range
Dim mySearchRange                       As Range
Dim i                                   As Integer
Dim j                                   As Integer
Dim k                                   As Integer
Dim intNumberRecordsRead                As Integer
Dim intNumberOfDuplicates               As Integer
Dim myWorksheet                         As Worksheet
Dim blnFoundDuplicate                   As Boolean
 
Const strStartRange = "A1"
Const strStartText = "Title"
 
    'First lets clear the previous report results
    Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading")
    Set myOutputRange = Range(myOutputRange.Offset(1, 0), myOutputRange.Offset(1, 0).End(xlToRight))
    Set myOutputRange = Range(myOutputRange, myOutputRange.SpecialCells(xlLastCell))
    myOutputRange.Clear
 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportLastRunDate").Value = "Running Report....Wait"
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfRecordsRead").Value = 0
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfDuplicates").Value = 0
 
    'Now lets go through the workbook and find each of the worksheets in the right format to create the duplicate list
    Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading")
    Set mySearchRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading")
    i = 1
    For Each myWorksheet In ThisWorkbook.Worksheets
        If myWorksheet.Name <> "Duplicate Report" Then
            If myWorksheet.Range(strStartRange).Value = strStartText Then
                Set myRange = myWorksheet.Range(strStartRange)
                j = 1
                Do While myRange.Offset(j, 1).Value <> ""
 
                    'Search for the record if it already exists then update the counts
                    k = 1
                    blnFoundDuplicate = False
                    Do While mySearchRange.Offset(k, 1).Value <> ""
                        If mySearchRange.Offset(k, 8).Value = myRange.Offset(j, 0).Value & " " & myRange.Offset(j, 1).Value & " " & myRange.Offset(j, 2).Value Then
                            mySearchRange.Offset(k, 6).Value = mySearchRange.Offset(k, 6).Value + 1
                            mySearchRange.Offset(k, 7).Value = mySearchRange.Offset(k, 7).Value & ", " & myWorksheet.Name
                            intNumberOfDuplicates = intNumberOfDuplicates + 1
                            blnFoundDuplicate = True
                            Exit Do
                        End If
                        k = k + 1
                    Loop
 
                    'If the record did not already exist then write it to the report
                    If Not blnFoundDuplicate Then
                        myOutputRange.Offset(i, 0).Value = myRange.Offset(j, 0).Value
                        myOutputRange.Offset(i, 1).Value = myRange.Offset(j, 1).Value
                        myOutputRange.Offset(i, 2).Value = myRange.Offset(j, 2).Value
                        myOutputRange.Offset(i, 3).Value = myRange.Offset(j, 3).Value
                        myOutputRange.Offset(i, 4).Value = myRange.Offset(j, 4).Value
                        myOutputRange.Offset(i, 5).Value = myRange.Offset(j, 5).Value
                        myOutputRange.Offset(i, 6).Value = 1
                        myOutputRange.Offset(i, 7).Value = myWorksheet.Name
                        myOutputRange.Offset(i, 8).Value = myRange.Offset(j, 0).Value & " " & myRange.Offset(j, 1).Value & " " & myRange.Offset(j, 2).Value
                        i = i + 1
                    End If
 
                    j = j + 1
                Loop
                intNumberRecordsRead = intNumberRecordsRead + j - 1
            End If
        End If
        Set myOutputRange = Range(myOutputRange, myOutputRange.End(xlToRight))
        Set myOutputRange = Range(myOutputRange, myOutputRange.SpecialCells(xlLastCell))
        With myOutputRange
            .Cells.Sort Key1:=.Columns(7), Order1:=xlDescending, Header:=xlYes, _
                        Key2:=.Columns(3), Order2:=xlAscending, _
                        Key3:=.Columns(2), Order3:=xlAscending
        End With
        Set myOutputRange = ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportStartHeading")
    Next
 
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportLastRunDate").Value = Now()
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfRecordsRead").Value = intNumberRecordsRead
    ThisWorkbook.Worksheets("Duplicate Report").Range("DuplicateReportNumberOfDuplicates").Value = intNumberOfDuplicates
 
    MsgBox "Duplicate Report has Completed", vbInformation, "Report Complete"
 
End Sub

Step 3 – Run the routine and review the Results

Now the routine can be run via the Tools > Macros options or you can create a Command Button on the worksheet to run the routine. Refer below to sample output from this routine to list duplicate names found in the workbook.

Here the report shows the names for each of the entries in the worksheets within the workbook. It also reports on the worksheet in which the name was found and how many times it was found.

Following many requests for assistance on this post we have now provided a link to the sample file so that you can apply this routine to your own situation. Click on the following link to download the sample file

Download Sample File

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment

Related posts:

  1. Unhide Multiple Worksheets
  2. Change Cell Formats using Ranges and Offset
  3. How to find the last used Cell in a Worksheet
  4. How to Lock and Protect all Worksheets in a Workbook
  5. How to Unlock and UnProtect all Worksheets in a Workbook

Discussion

28 responses to "Find Duplicates in Multiple Worksheets"

  • samy4movies says:

    I have a problem when i run the macro it jus stop at line

    I have like 25 worksheets and I would like to find duplicated names and create a report that will show where each name is coming from.
    How can I do this?
    Thanks for any help.
    Sincerely,
    Samy

  • SAMUEL LAZCANO says:

    Thanks for the respond,
    Does this macro “findDuplicates” will work when using excel in a Mac?
    Thanks

    • Excel Help Desk says:

      Samuel,
      This macro should work with any version of Excel on the MAC that supports macros.
      I believe that Excel 2010 for the MAC is the latest version that does support macros
      Thanks
      ExcelHelpDesk Support Team

  • Sonny Martin says:

    I am new to macros but this addresses my need. How do I find out to set it up for my workbook. I will email my workbook to support@exceldesk.com

  • vtd says:

    Hi Excel Help Desk
    Thanks very much. This is exactly what I need. However, I had difficulty putting it to work. Could you please send me an example workbook using this macro?

    • Excel Help Desk says:

      Hi vtd,
      We have now included a sample version of a workbook that implements this code. See the bottom of the post for that sample.
      Regards
      Excel Help Desk Support Team

  • Metrazal says:

    This is exactly what I need. However, I need to compare numbers not text. What needs to be changed to acomplish this?

    • Excel Help Desk says:

      Hi,
      To extend this macro to compare numbers instead of text, simply use the VAL function to convert text to numbers before the comparison
      Let us know if you need help with this.

      • Andy B says:

        I am doing a similar thing to this searching for duplicate invoice numbers, i am fairly novice to VBA in excel as i last looked at this in college over 10 years ago. How would I go about converting the text to number within the VBA code string?

      • Andy B says:

        How would you be able to remove duplicates within the report, i.e. i want the report to search the first sheet (invoice) and then the remaining sheets (account payments) and the duplicate report to show the open invoices that have not been paid, i can send the modified file i’m trying to implement this on if that would help, i’m close but not quite there.

        Thanks again, any help would be greatly appreciated.

        Andy

  • Jason says:

    Hi. I’m getting a:

    Run-time error ’1004′:
    Method ‘Value’ of object ‘Range’ failed

    Is there a limit on the number of rows per worksheet? I’m researching Twitter followers, trying to find duplicate connections among several users. Some of the users have up to 15k followers.

    Thanks in advance!

  • Brad says:

    I’ve copied the Code from the example of getting names from multiple reports and then recieved error messages saying no comments were allowed after end sub and several other notifications. How can I recreate the report you have in this example. I am a novice with Macro’s and may just not no which buttons to seect to get started please help.

    thanks

    • Excel Help Desk says:

      Brad,
      If you can send a sample file with the data that you are trying to find duplicates for to the support@excelhelpdesk.com email address we will look into this for.
      Also include a description of how the duplicates are to be determined..
      Regards
      Excel Help Desk

  • Darren says:

    Hi, This looks like a promising macro. I copied into my workbook, but when I run it I get a “Subscript out of range” response. I’m not familiar enough with VBA to know what I need to edit in the sample code. Any chance you could help? Thanks.

  • Aaron Costain says:

    Hi,

    I need to find duplicate account numbers in a spreadsheet and create a report telling me what is repeated, could you help?

    Aaron

    • Excel Help Desk says:

      Aaron,
      If you can send a sample file with the data that you are trying to find duplicates for to the support@excelhelpdesk.com email address we will look into this for.
      Also include a description of how the duplicates are to be determined..
      Regards
      Excel Help Desk

  • Bev Walker says:

    I’m trying to set this up with data that I’m using. Would you let me know what information I need to change in the code to make it work?

    • Excel Help Desk says:

      Bev,
      If you can send a sample file with the data that you are trying to find duplicates for to the support@excelhelpdesk.com email address we will look into this for.
      Also include a description of how the duplicates are to be determined..
      Regards
      Excel Help Desk

  • The Chase says:

    This is exactly what i’m looking for, however , how do i implement that function in my workbook (‘Run Duplicate Report’)

    • Excel Help Desk says:

      Chase,
      You simply copy the code into your workbook VBA editor and create the worksheet Duplicate Report and lastly setup the Names for the report.
      If you are having problems then send through your sample workbook and we will include the duplicate code to the workbook
      Regards
      ExcelHelpDesk

      • The Chase says:

        I’m still having trouble trying to fix this, I have two worksheets that have data that is collected daily, now, i would a macro that would check, by cell number (column E), what is in work sheet 2 and that is not in work sheet one. In simpler terms i want a macro that will show the difference between the sheets (sheet 1 has 31 rows, sheet 2 has 45 rows, the macro should highlight the 14 rows)

        Please Help
        Thanks

  • AS says:

    Hi,

    I am having kind of the same problem. The only difference is that the names of my two worksheets are not always exactly the same. For example, if in worksheet 1 I have “Chocolate”, in the worksheet 2 I have maybe “white chocolate”, but for my evaluation”chocolate” and “white chocolate” are the same, and I would like to have them as duplicate.
    Is there a method in order to find duplicates by just using part of a word?
    Is it possible to do it by using the approach described above?

    Thank you,
    AS

    • Excel Help Desk says:

      AS,

      Yes it will be possible to modify the comparison and checking for duplicates to match on part of a value. It would only really work if the value being compared was part of the value that was found not the otherway around.
      So if the value was “Chocolate” it would match to “Chocolate” and “White Chocolate” but not if the value being searched for was “White Chocolate” it would not match to “Chocolate”
      If you are still looking for help on this send through (support@excelhelpdesk.com) a sample workbook and we can update and include the matching code to find duplicates for you.

      Regards
      ExcelHelpDesk

Leave a Comment

Wrap code blocks with <pre lang="VB" > code goes here </pre> to appear with VBA formatting.



SUBSCRIBE WITHOUT COMENTING