Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted
{=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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.