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,

	A​	B​	C​	D​
1​	Begin​	End​	Check​	​
2​	0​	0​
3​	47000000001​	47000099999​	OK	C3: =IF((A3 > B2) * (B3 >= A3), "OK", "Not OK")
4​	47006900006​	47007220248​	OK
5​	47007220255​	47007430243​	OK
6​	48000100503​	48000239999​	OK
7​	48000625507​	48000770507​	OK
8​	48001180601​	48001280601​	OK
9​	296578100001​	296578200000​	OK

 

[2Ruff4U]

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

 

[shg]

	A​	B​	C​	D​
1​	Group​	Begin​	End​	Conflict​
2​	1​	48001180601​	48001280601​	OK​
3​	2​	47000000001​	47000099999​	5​
4​	3​	47007220255​	47007430243​	OK​
5​	4​	48000100503​	48000239999​	OK​
6​	5​	47000099999​	47007220248​	2​
7​	6​	296578100001​	296578200000​	OK​
8​	7​	48000625507​	48000770507​	OK​

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