New Number Range is Unique from Existing Ranges

[2Ruff4U]

Hello,
I track a large number of card ranges. These ranges are not sequential from one to the next and may not have the same number of digits in them. When I add a new range, I need to be sure that the new range does not have any values in it that overlaps any of the existing ranges. I would greatly appreciate thoughts on how to do this. Sample data below, a "Valid Range" simply means that the numbers in the range are unique to any other range:

Begin # End # Valid Range?
47006900006 47007220248
47007220255 47007430243
48000100503 48000239999
48001180601 48001280601
48000625507 48000770507
296578100001 296578200000
47000000001 47000099999
New Range Start # New Range End # Error Check

Are any numbers in the new range within any of the existing ranges?

 

[shg]

If you sort by Begin,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Begin​

[/td][td="bgcolor:#F3F3F3"]

End​

[/td][td="bgcolor:#F3F3F3"]

Check​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

47000000001​

[/td][td]

47000099999​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td="bgcolor:#CCFFCC"]C3: =IF((A3 > B2) * (B3 >= A3), "OK", "Not OK")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

47006900006​

[/td][td]

47007220248​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

47007220255​

[/td][td]

47007430243​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

48000100503​

[/td][td]

48000239999​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

48000625507​

[/td][td]

48000770507​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

48001180601​

[/td][td]

48001280601​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

296578100001​

[/td][td]

296578200000​

[/td][td="bgcolor:#CCFFCC"]OK[/td][td][/td][/tr]
[/table]

 

[2Ruff4U]

Thank you for your reply. I should have been clear that it will not be possible to sort the range values.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Group​

[/td][td="bgcolor:#F3F3F3"]

Begin​

[/td][td="bgcolor:#F3F3F3"]

End​

[/td][td="bgcolor:#F3F3F3"]

Conflict​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

48001180601​

[/td][td]

48001280601​

[/td][td="bgcolor:#CCFFCC"]

OK​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2​

[/td][td]

47000000001​

[/td][td]

47000099999​

[/td][td="bgcolor:#CCFFCC"]

5​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3​

[/td][td]

47007220255​

[/td][td]

47007430243​

[/td][td="bgcolor:#CCFFCC"]

OK​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

4​

[/td][td]

48000100503​

[/td][td]

48000239999​

[/td][td="bgcolor:#CCFFCC"]

OK​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

5​

[/td][td]

47000099999​

[/td][td]

47007220248​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6​

[/td][td]

296578100001​

[/td][td]

296578200000​

[/td][td="bgcolor:#CCFFCC"]

OK​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

7​

[/td][td]

48000625507​

[/td][td]

48000770507​

[/td][td="bgcolor:#CCFFCC"]

OK​

[/td][/tr]
[/table]

In D2, array-entered and copied down,

=IF(SUMPRODUCT((B2 <=$C$2:$C$8) * (C2 >= $B$2:$B$8)) = 1, "OK", INDEX($A$2:$A$8, MATCH(1, (A2 <> $A$2:$A$8) * (B2 <=$C$2:$C$8) * (C2 >= $B$2:$B$8), 0)))

 

[2Ruff4U]

Thank you very much, this work perfectly! Already discovered a problem that could have been bad down the road!

 

[shg]

Simpler: =IFERROR( INDEX($A$2:$A$8, MATCH(1, (A2 <> $A$2:$A$8) * (B2 <= $C$2:$C$8) * (C2 >= $B$2:$B$8), 0)), "OK")