Home

Excel 2007 search across multiple workshets in one workbook for all duplicates (macro or VBA code?)

%3CLINGO-SUB%20id%3D%22lingo-sub-744892%22%20slang%3D%22en-US%22%3EExcel%202007%20search%20across%20multiple%20workshets%20in%20one%20workbook%20for%20all%20duplicates%20(macro%20or%20VBA%20code%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744892%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20assistance%20in%20setting%20up%20a%20conditional%20format%20(worksheet%20within%20the%20workbook)%20to%20conditionally%20format%20for%20duplicates%20looking%20at%2015%2B%20worksheets.%26nbsp%3B%20Just%20need%20to%20color%20both%20cells%2C%20not%20remove%20or%20otherwise%20change%20-%20just%20show%20the%20duplicate(s)%2C%20if%20any.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20that%20it%20is%20a%20simple%20solution%2C%20but%20my%20knowledge%20of%20VBA%20is%20limited.%26nbsp%3B%20If%20a%20macro%20would%20do%2C%20then%20that%20is%20great.%26nbsp%3B%20I%20just%20need%20the%20simplest%20answer%20(as%20the%20user%20is%20not%20as%20savvy%20with%20Excel%202007).%26nbsp%3B%20Any%20and%20all%20help%20(directions%2C%20code%2C%20etc)%20would%20be%20most%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20everyone!%3C%2FP%3E%3CP%3EScott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744892%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECnditional%20Formatting%20Duplicates%20Multiple%20Sheets%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746568%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202007%20search%20across%20multiple%20workshets%20in%20one%20workbook%20for%20all%20duplicates%20(macro%20or%20VBA%20cod%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373382%22%20target%3D%22_blank%22%3E%40S_Ford%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20guessing%20you%20might%20like%20to%20check%20the%20same%20range%20in%20a%20number%20of%20different%20worksheets%20for%20duplicates.%20If%20so%2C%20you%20might%20use%20a%20Conditional%20Formatting%20formula%20like%3A%3C%2FP%3E%3CP%3E%3D(COUNTIF(Sheet1!A%241%3AA%2410%2CA1)%2BCOUNTIF(Sheet2!A%241%3AA%2410%2CA1)%2BCOUNTIF(Sheet3!A%241%3AA%2410%2CA1)%2B...)%26gt%3B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuch%20a%20formula%20is%20pretty%20ugly%20when%20you%20need%20to%20watch%2015%20worksheets%2C%20however.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%2C%20you%20might%20consider%20using%20a%20different%20workbook%20layout%20so%20the%20values%20are%20reported%20back%20to%20different%20columns%20on%20the%20same%20worksheet.%20You%20could%20do%20so%20by%20copying%20down%20formulas%20like%3A%3C%2FP%3E%3CP%3E%3DIF(Sheet2!A2%3D%22%22%2C%22%22%2CSheet2!A2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20worksheet%20is%20called%20Master%2C%20you%20could%20use%20a%20Conditional%20Formatting%20formula%20in%20your%20target%20worksheets%20to%20test%20the%20values%20reported%20back%20to%20Master%20like%3A%3C%2FP%3E%3CP%3E%3DCOUNTIF(Master!%24A%241%3A%24O%24100%2CA1)%26gt%3B1%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hello All:

 

I need assistance in setting up a conditional format (worksheet within the workbook) to conditionally format for duplicates looking at 15+ worksheets.  Just need to color both cells, not remove or otherwise change - just show the duplicate(s), if any. 

 

I suspect that it is a simple solution, but my knowledge of VBA is limited.  If a macro would do, then that is great.  I just need the simplest answer (as the user is not as savvy with Excel 2007).  Any and all help (directions, code, etc) would be most appreciated.

 

Thanks to everyone!

Scott

1 Reply

@Deleted 

I am guessing you might like to check the same range in a number of different worksheets for duplicates. If so, you might use a Conditional Formatting formula like:

=(COUNTIF(Sheet1!A$1:A$10,A1)+COUNTIF(Sheet2!A$1:A$10,A1)+COUNTIF(Sheet3!A$1:A$10,A1)+...)>1

 

Such a formula is pretty ugly when you need to watch 15 worksheets, however.

 

As an alternative, you might consider using a different workbook layout so the values are reported back to different columns on the same worksheet. You could do so by copying down formulas like:

=IF(Sheet2!A2="","",Sheet2!A2)

 

Assuming that worksheet is called Master, you could use a Conditional Formatting formula in your target worksheets to test the values reported back to Master like:

=COUNTIF(Master!$A$1:$O$100,A1)>1