Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

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