Highlight duplicate values
Results 1 to 5 of 5

Thread: Highlight duplicate values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Highlight duplicate values

    Hi

    I have a worksheet that I have numerous data in and sometimes they get duplicated.

    Is there anyway that I can highlight the duplicate values that are exactly the same in columns A-D. Ive tried conditional formatting on excel and that highlights all duplicate values in each individual column but I need it to highlight if a duplicate is all the way across all 4 columns.

    It will also need to look all the way down the workbook for the duplicate entries.

    Any ideas?

    Thanks

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,663
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How can I highlight duplicate values

    Duplicate values or duplicate sets/rows of data?
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  3. #3
    Board Regular
    Join Date
    Jul 2005
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I highlight duplicate values

    Sorry I meant duplicate rows of data.

    Lets say row 1 and row 99 have exactly the same data in columns A-D I need them highlighted or better still if it can be done in column E put the word DUPLICATE beside all the ones that are duplicated.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How can I highlight duplicate values

    How about something like
    =COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1,$D:$D,$D1)>1
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I highlight duplicate values

    Of, adding to Fluff, if you want the word in E:

    Code:
    =IF(COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1,$D:$D,$D1)>1,"DUPLICATE","")
    Last edited by kweaver; Jul 16th, 2019 at 11:23 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •