This is a discussion related to my post in the Formulas forum. Here: http://www.ozgrid.com/forum/showthread.php?t=199398
I will post a reply there with some additional info. and a link to this post.
(Note: The following uses code tags formatting (i.e., preserve spaces).
Background:
For a large exported data set, I needed to determine if the current row's number
range overlapped with the next row's number range. For example, if you have some
manufactured items with similar model and styles grouped together with different
lot numbers, it can be of interest to know if the lot numbers overlap which would
indicate a problem with the data.
Originally, I just wanted a formula to use on a spreadsheet that would indicate
if two number ranges overlapped. When I searched, all that I found was:
* Statements similar to "Cant't do it with a boolean expression. You need need a computer program."
* VBA routines.
* Other prgramming language pseudo routines
* Pseudo formulas similar to:
- IF(A>C AND A<D Then True) ...
- =if(a=c,true,if(a=d,true,(b=c,true,if(...
My final answer is based on what follows hereafter.
With two columns representing a range of numbers:
Column A = the begin of a number range
Column B = the end of a number range
I used the following table/Visual of conditions defining if two ranges overlap to help figure out a
worksheet formula.
For two number ranges A1 thru B1 and A2 thru B2:
Visual Overlap Conditions for True How they overlap------------ --------------------------- ---------------------------A1....B1 (B1 > A2) AND (B1 < A2) A1-B1 overlaps A2-B2 at one end.......A2....B2.......A1....B1 (B2 > A1) AND (B2 < B1) A1-B1 overlaps A2-B2 at other end Which gives a partial Boolean condition for True: ((B1 > A2) AND (B1 < A2)) OR ((B2 > A1) AND (B2 < B1)) Note that the overlap shown is visually more than exactly at the number range boundaries. It seemed a bit complicated to try using >= or =< due to the AND operators. Also, my boolean algebra has long ago become rusty. The Excel worksheet formula: =OR(AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))When the ranges overlap only at one or more boundaries or are an exact match, I used the following:Visual Overlap Conditions for True How they overlap-------------- --------------------------- ------------------------------------A1....B1 (A1 = A2) OR (B1 = B2) Upper or lower boundaries matchA2....B2 (or an exact match)A1....B1 (B1 = A2) One upper and lower boundary matches......A2....B2......A1....B1 (A1 = B2) Other upper and lower boudary matchesA2....B2 Which gives another partial Boolean condition for True: (A1 = A2) OR (B1 = B2) OR (B1 = A2) OR (A1 = B2) Excel worksheet formula: =OR(A1=A2,B1=B2,B1=A2,A1=B2)Combining the two partials and using Excel's worksheet formulas with Cells A1, B1 are the first begin and end rangeCells A2, B2 are the second begin and end range:Worksheet formula =OR(A1=A2,B1=B2,B1=A2,A1=B2,AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))
It displays True if the first range overlaps the second and False if not.
You can use filtering, additional helper columns, etc. to further the data manipulation.
I suppose a better, more structured database and data translation to my own database
would be more exacting. However, I just wanted a worksheet since it would be much
more of a project than is practical for me at this time.
Jim