+ 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 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
    27,903

    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

    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
    2016
    Posts
    3,834

    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