+ Reply to Thread
Results 1 to 3 of 3

Highlight duplicates, multiple columns

  1. #1
    Registered User
    Join Date
    09-14-2016
    Location
    Aarhus, Denmark
    MS-Off Ver
    2013
    Posts
    18

    Highlight duplicates, multiple columns

    Hi all

    I have a sheet with 12 columns. I would like to highlight rows where the data in 4 of these columns is identical/duplicates. Below is an example:

    Day Start Weeks Location
    mo 10:00 35 1485 - 239
    tu 09:00 35 1482 - 50
    mo 10:00 35 1485 - 239
    fr 11:00 38 1272 - 560

    Because row 2 and 4 is totally identical I want a way to highlight these.

    In advance, thank you for your help.

    Kind regards Chris

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Highlight duplicates, multiple columns

    Hi,

    Use a helper column E and concatenate A1:D1. i.e. =A1&B1&C1&D1
    Copyh this down

    Then alongside the helper column enter the formula in F1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copy this down and filter for the word 'duplicate'
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Highlight duplicates, multiple columns

    Select the A2:D5 column ->>> Press Alt->> O D->>> you will get a window Conditional formatting Rules Manager->> Click on new rule->>>you will get another window name New formatting rule->>>select use a formula to determine which cells to format---->put in the =SUMPRODUCT(--($A2&$B2&$C2&$D2=$A$2:$A$5&$B$2:$B$5&$C$2:$C$5&$D$2:$D$5))>1 in formula section then select the format->>> format the cells what you like to do. then Ok->> Apply->> ok.

    Same thing you will have to do for N column also but only formula will be change that would be =COUNTIF($B$1:$B$8,$N1) and all done.

    Check the attached file.

    Hope this will help you.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ 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. [SOLVED] VBA To Highlight Duplicates against a Master list across multiple columns
    By Bagpuss1234 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-08-2015, 11:22 AM
  2. Using filter to highlight/filter duplicates in multiple columns but within 1 day
    By DaveBre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2014, 04:07 AM
  3. [SOLVED] Highlight Duplicates Between Multiple Columns
    By WorkwearExp in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2014, 08:12 AM
  4. How to compare two columns and highlight and rows containing duplicates
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2014, 04:47 PM
  5. Highlight Duplicates from Two Columns with Different Colours
    By ddgacic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-24-2012, 06:22 AM
  6. Replies: 5
    Last Post: 07-05-2011, 06:25 PM
  7. find duplicates in two columns and highlight whole row
    By stryker9603 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2010, 01:46 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