+ Reply to Thread
Results 1 to 9 of 9

Highlighting duplicates across 8 work sheets

  1. #1
    Registered User
    Join Date
    02-24-2014
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    11

    Highlighting duplicates across 8 work sheets

    Hi, does anyone know if it's possible to highlight duplicates across 8 worksheets?

    I will have a table of 30 ID numbers on each sheet, and I want it highlighted in colour if there are two of the same.

    Basically the same function as condition formatting, but I can only get that to work on one sheet.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,999

    Re: Highlighting duplicates across 8 work sheets

    If you have a CF that works on one sheet, you can copy the range with the CF and pastspecial formats to the other sheet.

    You can also use the Format Painter for this.

  3. #3
    Registered User
    Join Date
    02-24-2014
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Highlighting duplicates across 8 work sheets

    Thanks for that. I tried the format painter but it only compares the values on each sheet.

    For the condition formatting, the applies to has it as =$C$3:$C$98

    How would I write that formula so it covered the same cell range on all 8 sheets, the sheets are names 1,2,3,4,5,6,7 and 8?

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,999

    Re: Highlighting duplicates across 8 work sheets

    There seems to be some confusion,
    1st
    "...I tried the format painter but it only compares the values on each sheet.

    2nd

    "How would I write that formula so it covered the same cell range on all 8 sheets, the sheets are names 1,2,3,4,5,6,7 and 8? "

  5. #5
    Registered User
    Join Date
    02-24-2014
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Highlighting duplicates across 8 work sheets

    Apologies, my choice of words is poor.

    I'm wanting it to compare the values across all 8 sheets, so for example if one value is in sheet 1 and duplicate in 8 it'll the condition formatting will kick in and it will appear red.

  6. #6
    Registered User
    Join Date
    02-24-2014
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Highlighting duplicates across 8 work sheets

    Bump.

    Does anyone have any ideas?

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,329

    Re: Highlighting duplicates across 8 work sheets

    It sounds like we could use a small sample workbook (not picture or screenshot) representative of what you are trying to do.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  8. #8
    Registered User
    Join Date
    02-24-2014
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Highlighting duplicates across 8 work sheets

    Please find attached a copy of the desensitised excel sheet which I'm trying to compare.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,890

    Re: Highlighting duplicates across 8 work sheets

    As modeled on 'Sheet1', if you want to do a quick check for duplicates you could:
    1) add another sheet,
    2) populate row one with the original sheet names,
    3) populate a range using: =INDIRECT(A$1&"!A"&ROW())
    4) apply conditional formatting to that range using: =IF(A2=0,FALSE,COUNTIFS($A$2:$G$13,A2)>1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Highlighting duplicates
    By Jveto in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2016, 01:32 PM
  2. Replies: 17
    Last Post: 02-01-2016, 04:17 AM
  3. Highlighting duplicates across sheets
    By msmiss in forum Excel General
    Replies: 1
    Last Post: 04-14-2015, 09:35 PM
  4. Highlighting duplicates
    By nick2price in forum Excel General
    Replies: 10
    Last Post: 08-30-2014, 10:15 AM
  5. Replies: 6
    Last Post: 02-16-2013, 07:29 AM
  6. Highlighting every other set of duplicates
    By IUgrad04 in forum Excel General
    Replies: 3
    Last Post: 12-27-2011, 08:08 AM
  7. Help with Highlighting all duplicates in a row
    By Jimv in forum Excel General
    Replies: 4
    Last Post: 04-21-2005, 03:06 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