Highlight duplicate values

ghrek

Active Member
Joined
Jul 29, 2005
Messages
426
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Re: How can I highlight duplicate values

Duplicate values or duplicate sets/rows of data?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top