I have a table that has a column where the operators can set ranges like 1-12. They can set a range in each row. I need to be able to check that non of the ranges cross in to each other.
Example
1-12
13-22
21-45
I need to be able to figure out that in this example, the 3rd range has a duplicate number “21 & 22”
Does anybody know how to check for this in a list of ranges?
Thanks
If the numbers
1 12
13 22
21 45
are in cells A1:B3 of an Excel sheet, I’d put the formula
=IF(MAX($A$1:B1) <= $A2, “problem”, “”) in C2 and drag it downwards.
Here’s something to get you started. It returns information about the first overlap encountered (if any) working through the ranges from the beginning.
on checkRanges(theRanges)
set verdict to {ranges:{}, |common values|:{}}
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "-"
repeat with i from 1 to (count theRanges)
set thisRange to text items of item i of theRanges
set item i of theRanges to thisRange
set {a, b} to thisRange
repeat with j from 1 to i - 1
set {c, d} to item j of theRanges
considering numeric strings
if ((a ≥ c) and (a ≤ d)) or ((b ≥ c) and (b ≤ d)) or ((a < c) and (b > d)) then
set verdict's ranges to {j, i}
repeat with k from a to b
if ((k ≥ c) and (k ≤ d)) then set end of verdict's |common values| to k
end repeat
exit repeat
end if
end considering
end repeat
if ((count verdict's ranges) > 0) then exit repeat
end repeat
set AppleScript's text item delimiters to astid
return verdict
end checkRanges
set ranges to {"1-12", "13-22", "21-45"}
checkRanges(ranges)
--> {ranges:{2, 3}, |common values|:{21, 22}}