Table Range verification

  • Thread starter Thread starter Alberto Ast
  • Start date Start date
A

Alberto Ast

How can I validate there is no overlap of ranges... Col A has date code, Col
B has the start range number and Col C has the ending range number...

In a given date code I want to make sure there is no overlap or ranges... on
below example 2nd date code is equal to 3rd date code but ranges overlap from
40 to 50.. how can I validate it will not happen?

Date Code Start End
00109FMLL 00001 00350
21809FMLL 00001 00050
21809FMLL 00040 00200
01509FMLL 00200 00300

Thanks
 
Hello Alberto,

Array-enter into D1:
=MAX(FREQUENCY(IF($A$1:$A$4=A1,$B$1:$B$4),IF($A$1:$A$4=A1,$C$1:$C$4)))
and copy down to D4.

Please notice that you need to array-enter this (enter with CTRL+SHIFT
+ENTER, not only with ENTER!).

All resulting numbers should be 1. Any higher number than 1 indicates
an overlapping range for the corresponding date code.

Regards,
Bernd
 
Thanks... this is great..... it does fix my problem... I put it into my file
and works great.... have one more question... is there a way to do it not
using arrays..? they are great but I am NOT well knowledgeble of them and
limit some of my capabilities to do some other validations.
 
Back
Top