# Excel unique value in range

Hi I need to ensure the values unique in range with if possible with a high performance way. Sample data as shown below:

`````` A                 B
1 Serial From   -   Serial To
2 364500            364600
3 326900            326940
4 120000            120300
5 154700            154800
6    460               480
``````
• Table is unsorted
• There are two numbers in each row which always A is less than B
• I always enter new values to next empty row

There musnt be any number that match the any range above. If I enter 120150 and 120700 (because of 4th row) it must give error. How could I do that? I use Excel 2007. Thanks

-

## 1 Answer

``````{=IF(SUM(((A6>=\$A\$1:A5)*(A6<=\$B\$1:B5))+((B6>=\$A\$1:A5)*(B6<=\$B\$1:B5)))>0,"OVERLAP","")}
``````

If you put this in C6 and you put your next numbers in A6 and B6, C6 will say 'overlap' if any do.

Don't type the curly braces {}. Instead enter the formula using control+shift+enter, because it's an array formula.

-
Thanks for reply. One more question: For array fomulas why autofilling formulas dont work? – Gok Demir Feb 3 '10 at 11:27
Autofilling should work on array formulas just like regular formulas. – dkusleika Feb 4 '10 at 14:56