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

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

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

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

Worked a treat, thank you Pete.

Regards
John

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