Highlighting Duplicate Values within N rows

jonnyg860

New Member
Joined
Apr 7, 2011
Messages
2
I have a worksheet where employees scan barcodes of ISBN and Price into two columns. Right now I have a simple conditional format to highlight duplicates within each row. Is it possible to highlight duplicate values found within say 10 rows, rather than the entire column. I want it to alert them if they mistakenly scanned the same book twice, but not if it was same title scanned hours earlier. We use office 2010 with windows 7.

Thanks for any help.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
You can use this for your conditional formatting starting in the cell on the 10th row and then use the format painter to copy that format down your worksheet.

As the cell moves down, the range will also move down so that the conditional formatting will still work. The test for "" prevents all the blank spaces from being highlighted.

IF(A10="",0,COUNTIF(A1:A10,A10))>1
IF(A11="",0,COUNTIF(A2:A11,A11))>1
IF(A12="",0,COUNTIF(A3:A12,A12))>1

The only drawback is that you can't use this in Rows 1-9 because if you move backwards, the cell tries to reference A0 and then A-1, which are not valid cell names and you'll get a #REF error, but this will work for all but the first 9 rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,042
Messages
5,526,420
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top