Table Range verification
 

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

Table Range verification
 

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

Table Range verification
 

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.

"Bernd P" wrote:

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

Table Range verification
 

Hello Alberto,

I checked for a non-array solution shortly:
http://sulprobil.com/html/overlapping_ranges.html

But so far I think that for the given complexity of your task you will
need an array function or a special purpose UDF (macro).

Regards,
Bernd