 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Identifying Duplicates in Multiple Sheets
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 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. > this is probably an easy one for you guys: > [quoted text clipped - 10 lines] > > Tom 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) > Select the column of the first sheet containing the email addresses > (assumed to be column A for this example) and choose Data > Conditional [quoted text clipped - 25 lines] > > > > Tom thanks a million. > For the last part you could enter the following formula (in e.g. D2) > and fill down: [quoted text clipped - 33 lines] >> > >> > Tom No problem - thanks for the reply! > thanks a million. > [quoted text clipped - 35 lines] > >> > > >> > Tom
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.