+ Reply to Thread
Results 1 to 4 of 4

Finding Duplicates Across Multiple Worksheets in Excel

  1. #1
    Registered User
    Join Date
    11-13-2017
    Location
    dallas
    MS-Off Ver
    365
    Posts
    6

    Finding Duplicates Across Multiple Worksheets in Excel

    I have the VBA code below that I have used successfully to highlight the duplicates across 2 worksheets and for a single column range. I am wanting to highlight the duplicates across 3+ worksheets for the same column range within each worksheet. The formula I have used for highlighting across 2 sheets is below. Any assistance would be greatly appreciated.

    Hi TMS.
    Since you helped me so much the first time with this VBA, would you know how I can modify it to highlight the duplicates between 3 worksheets within the same workbook? The formula works to highlight for 2 worksheets, but I am not sure what to add to run it for more than 2 worksheets; there is only a single column in each that I am comparing.

    Thanks in advance for any guidance.

    The formula I have used for 2 worksheets is as follows:
    Sub findDuplicates()
    Dim rng1, rng2, cell1, cell2 As Range

    Set rng1 = Worksheets("Week 1").Range("B1:B200")
    Set rng2 = Worksheets("Week 2").Range("B1:B200")
    For Each cell1 In rng1
    For Each cell2 In rng2
    If IsEmpty(cell2.Value) Then Exit For
    If cell1.Value = cell2.Value Then

    cell1.Font.Bold = True
    cell1.Font.ColorIndex = 2
    cell1.Interior.ColorIndex = 3
    cell1.Interior.Pattern = xlSolid
    cell2.Font.Bold = True
    cell2.Font.ColorIndex = 2
    cell2.Interior.ColorIndex = 3
    cell2.Interior.Pattern = xlSolid

    End If
    Next cell2
    Next cell1
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Finding Duplicates Across Multiple Worksheets in Excel

    You need to be clear on the comparisons you have to make. Is it just 1 vs 2 and 1 vs 3? Or do you also check 2 vs 3?

    And can there be more than one occurrence in any one sheet? That is, are there duplicates within sheet 1, duplicates within sheet 2, and duplicates within sheet 3?

    I'd be inclined not to use VBA. You could use Conditional Formatting with a Formula. And you would use COUNTIF, or COUNTIFS, in that formula to determine whether a value is duplicated or not ( COUNTIF() = 0 would indicate a unique value.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-13-2017
    Location
    dallas
    MS-Off Ver
    365
    Posts
    6

    Re: Finding Duplicates Across Multiple Worksheets in Excel

    Thanks for your reply!
    I am needing to compare 1v2, 1v3 and 2v3. There won't be more than one occurrence in any sheet.

    Yes, a countif would work! Thanks so much again for your assistance and quick reply. It is very much appreciated.

    I would click your star again to Add to your Reputation if it would let me, but it tells me to spread thanks around, but you are the only one who has helped/responded to me. Best I can do is thank you again!
    Last edited by MSafir; 11-15-2017 at 07:04 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Finding Duplicates Across Multiple Worksheets in Excel

    You're welcome. Don't worry, a thank you is enough

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding Duplicates Across Multiple Workbooks.
    By sturmy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 10:49 AM
  2. [SOLVED] Finding Duplicates Across Multiple Workbooks.
    By sturmy in forum Excel General
    Replies: 0
    Last Post: 04-24-2014, 09:43 AM
  3. Replies: 0
    Last Post: 09-18-2013, 08:00 AM
  4. [SOLVED] Finding duplicates throughout multiple columns
    By goldstar1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 09:37 PM
  5. Finding duplicates in multiple columns
    By khank in forum Excel General
    Replies: 2
    Last Post: 12-30-2010, 06:54 PM
  6. [SOLVED] Finding duplicates across worksheets
    By JimDandy in forum Excel General
    Replies: 1
    Last Post: 10-04-2006, 10:52 AM
  7. Finding duplicates in 2 worksheets
    By darkwood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2006, 03:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1