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