Excel Macro To Compare All WorkSheets In An Excel Workbook, highlight duplicates, generate new sheet reporting the duplicates (sheet name, line number) RRS feed

  • Question

  • Hello,

    I need assistance in creating an Excel Macro that would look at all worksheets in one workbook (.xls or .xlsx), locate, hightlight both duplicates then generate a separate sheet that would report back the duplicate cells and which tab (worksheet) the offending cells are located.

    Note, the query needs to not case sensitive nor ignore a cell if there are leading spaces or superfluous characters leading the cell or after the main data...   For example:

    Sheet 1


    Sheet 2

    B2=[blank cells]19abc123

    Sheet 3

    B3=19ABC123_ (or blank cells)

    Approximate number of worksheets in the workbook average around 20.  Any help would be most appreciated. Thank you so very much!!

    Scott F.

    Wednesday, September 11, 2019 5:39 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Emi Zhang

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, September 12, 2019 1:29 AM
  • To:  Scott
    re:  compare across multiple sheets

    You may be trying to go a bridge too far.
    If you have 1000 data cells on each sheet (10 columns x 100 rows),
    comparing all cells would require ~ 2,000,000 comparisons.

    Adding in a requirement to do "fuzzy" matches makes for a very heavy overhead.
    Microsoft released a Fuzzy Match Excel add-in (last updated in 2014) that is very complicated.
    (some complaints as to "won't install")

    Removing extra spaces on your data before trying to find duplicates should be your first step.
    There is existing code to find dupes on a single sheet.

    Thursday, September 12, 2019 4:50 PM