1

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

Improve this question

1 Answer 1

Reset to default
1 
{=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.

Improve this answer
2
  • Thanks for reply. One more question: For array fomulas why autofilling formulas dont work?
    – Gok Demir
    Feb 3, 2010 at 11:27
  • Autofilling should work on array formulas just like regular formulas.
    – dkusleika
    Feb 4, 2010 at 14:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .