Identifying Duplicates in Multiple Sheets

[Tom Gerling]

this is probably an easy one for you guys:

how do I highlight duplicate e-mail-adresses in multiple sheets within a
workbook? Is there a way to not only highlight the duplicates, but also
indicate the sheet(s) that contains their duplicate(s)?

We need to combine our existing mailing lists into one workbook for an
upcoming event. I removed duplicates in every individual list (=sheet) and
now need to find duplicates across sheets, so people don't get mailed twice.
Lists have only one column, no heading, very simple.

Thanks in advance,

Tom

 

[Lori]

Select the column of the first sheet containing the email addresses
(assumed to be column A for this example) and choose Data > Conditional
Formatting with condition Formula Is

=SUM(COUNTIF(INDIRECT("'"&Sheets&"'!A:A"),A1))>1

and format (e.g. with Fill colour yellow). Then to define the list of
sheet names choose Insert > Name > Define then enter: 'Sheets' and
refers to: '=GET.WORKBOOK(1)'.

Now copy formats to other sheets by right clicking on the sheet tab and
choosing Select all sheets (or holding down shift and selecting the
last sheet) and with column A still selected choose Edit > Fill >
Across Worksheets > Formats.

 

[Lori]

For the last part you could enter the following formula (in e.g. D2)
and fill down:

=LOOKUP(2,1/COUNTIF(INDIRECT(sheets&"!A:A"),A2),sheets)

 

[Tom Gerling]

thanks a million.

For the last part you could enter the following formula (in e.g. D2)
and fill down:

=LOOKUP(2,1/COUNTIF(INDIRECT(sheets&"!A:A"),A2),sheets)

 

[Lori]

No problem - thanks for the reply!

thanks a million.