+ Reply to Thread
Results 1 to 6 of 6

Highlighting duplicates in individual rows

  1. #1
    Registered User
    Join Date
    12-04-2019
    Location
    Ireland
    MS-Off Ver
    365 pro plus
    Posts
    3

    Highlighting duplicates in individual rows

    Hi there,

    I have a single sheet with 15 columns (A:O) and it has over 3,000 rows.

    There are only 51 different cell value possibilities, so the sheet has lots of duplicate cells.

    However, each individual row should not have duplicate cells, so I need to eliminate these rows.

    So I'm looking for a formula or conditional formatting to highlight all the rows with duplicate cells, where each row is treated individually with respect to the formula.

    So, if a row has 15 unique cells/values it's ok, but if any of the 15 cells has a duplicate value I need to highlight that row (or even just the duplicate cells) and subsequently delete it from the sheet.

    I know I can use conditional formatting to remove duplicate cells from each row, but I want need a solution that will prevent me having to go through the sheet row by row.

    Hope this makes sense and thanks in advance,
    J

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,803

    Re: Highlighting duplicates in individual rows

    Do you have numeric values in those cells, or text values ?

    Please attach a sample Excel workbook, so that we don't have to spend time entering some fictitious data ourselves before being able to play about with some formulae - details of how to do this are given in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-04-2019
    Location
    Ireland
    MS-Off Ver
    365 pro plus
    Posts
    3

    Re: Highlighting duplicates in individual rows

    Thanks Pete.

    Text values.

    I have now attached a small sample of the data.

    Rows 4 & 6 have duplicate entries at L4 and LO and M6 and O6.

    So I want to highlight these rows or just the offending cells so I can easily delete these rows from the sheet.

    I would be happy with a forumla in col P beside each row that gives a true or false as to whether there are duplicates.

    NB duplicates between different rows are ok, it's only duplicates in one single row that need to be eliminated.

    Thanks
    John
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,803

    Re: Highlighting duplicates in individual rows

    If you want a formula in column P to determine if there are duplicates or not in a particular row, then you can use this array* formula in P1:

    =SUM(COUNTIF(A1:O1,A1:O1))>15

    *NOTE than an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

    When you copy this down it will return TRUE or FALSE, so you can then decide which rows to delete.

    If you want to use Conditional Formatting to show the duplicates, then you should select all the data (i.e. from A1 to O6 in this example file) so that you can apply this in one operation, then click on Conditional Formatting | New Rule | Use a formula... , and then put this formula in the dialogue box which pops up:

    =COUNTIF($A1:$O1,A1)>1

    Click on the Format button | Fill tab and choose your colour (e.g. red), then click OK twice to exit the dialogue box and apply the conditions - Excel will automatically adjust the cell references to suit all the cells that you had selected, and you should see the offending cells highlighted quite clearly (better than doing it for the whole row).

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-04-2019
    Location
    Ireland
    MS-Off Ver
    365 pro plus
    Posts
    3

    Re: Highlighting duplicates in individual rows

    Worked a treat, thank you Pete.

    Regards
    John

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,803

    Re: Highlighting duplicates in individual rows

    Glad to help - thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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