Find Overlapping Data Ranges for Miles and Yards

[MrAStanley]

Hi Guys,

I am really hoping somebody can help me with this as I have tried everything but still cannot find a viable solution.

My goal is to be able to identify overlapping mileage (miles and yards) from within my excel file.

Please see below an example of the data...

(Please ignore the ELR and TID values for now)

I would like to be able to "flag up" rows which have overlapping mileage. As you can see, one start value would be 'Miles' and 'Yards' together (0.1) and the end would also be 'Miles' and 'Yards' together (10.0).

I figured that a Concatenate formula would get these together to form a more manageable value which is fine. However, I simply cannot find a solution to the initial problem of being able identify the overlapping data ranges.

We have an overlap here in the first 2 rows, as 9.0 - 11.0 also overlaps with the row above 0.1 - 10.0. This is one mile of overlap.

Another example image which may better explain the overlap issue can be seen here...

Is there is formula that can be used to achieve this outcome? I've never really been any good at Excel so I'm really hoping that an expert here can help me out.

Hopefully this all makes sense. The ELR and TID we can check by eye at this stage, I just need to get the overlapping mileages to highlight, in order to avoid overlapping sections.

Thank you in advance.

Andrew.

 

[Eric W]

Welcome to the MrExcel forum!

The usual formula for calculating the overlap of 2 ranges is to take the minimum of the 2 end values, and subtract the maximum of the 2 start values. See D9 below. Unfortunately, that doesn't translate well when you apply it to ranges of ranges. The I2 formula works, but it's pretty awkward.

Book1
	A	B	C	D	E	F	G	H	I
1				Miles	Yards	Miles	Yards
2				0	1	10	0		TRUE
3				9	0	11	0		TRUE
4				11	0	12	0		FALSE
5				12	0	13	0		FALSE
6
7
8	Start	End		Overlap
9	10	15		3
10	12	18
11
Sheet20

Cell Formulas
Range	Formula
I2:I5	I2	=ROWS(FILTER(D$2:D$5,(IF(F2+G2/10000<F$2:F$5+G$2:G$5/10000,F2+G2/10000,F$2:F$5+G$2:G$5/10000)-IF(D2+E2/10000>D$2:D$5+E$2:E$5/10000,D2+E2/10000,D$2:D$5+E$2:E$5/10000))>0))>1
D9	D9	=MAX(0,MIN(B9,B10)-MAX(A9,A10))

 

[Toadstool]

Hi MrAStanley,

As the MAX, MIN, MAX approach has already been described and as I only have Excel 2016 I thought I'd take a different approach and use your overlap diagram to identify which rows of data overlap. I hope you excuse the helper columns I and J.

MrAStanley.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ELR	TID		Miles	Yards	Miles	Yards		Yards	Yards		Overlapped Rows
2	X	X		0	1	10	0		1	17600		3	6	7
3	X	X		9	0	11	0		15840	19360		2	6	7
4	X	X		11	0	12	0		19360	21120		6	7
5	X	X		12	0	13	0		21120	22880		7
6	X	X		8	0	11	400		14080	19760		2	3	4	7
7	X	X		1	0	22	0		1760	38720		2	3	4	5	6
8	X	X		22	0	23	0		38720	40480
9	X	X		23	0	27	800		40480	48320		10
10	X	X		25	0	25	400		44000	44400		9
2nd

Cell Formulas
Range	Formula
I2:I10	I2	=D2*1760+E2
J2:J10	J2	=F2*1760+G2
L2:Q10	L2	=IFERROR(AGGREGATE(15,6,ROW($I$2:$I$10)-ROW($I$1)/(((($I2>$I$2:$I$10)*($I2<$J$2:$J$10))+(($I2<$I$2:$I$10)*($J2>$I$2:$I$10))+(($I2>$I$2:$I$10)*($J2<$J$2:$J$10))+(($I2<$I$2:$I$10)*($J2>$J$2:$J$10))>0)),COLUMN()-COLUMN($K$2))+ROW($L$1),"")

 

[Eric W]

My original formula can be adapted to earlier versions of Excel:

Book1
	A	B	C	D	E	F	G	H	I	J
1				Miles	Yards	Miles	Yards
2				0	1	10	0		TRUE	TRUE
3				9	0	11	0		TRUE	TRUE
4				11	0	12	0		FALSE	FALSE
5				12	0	13	0		FALSE	FALSE
6
7
8	Start	End		Overlap
9	10	15		3
10	12	18
Sheet20

Cell Formulas
Range	Formula
I2:I5	I2	=ROWS(FILTER(D$2:D$5,(IF(F2+G2/10000<F$2:F$5+G$2:G$5/10000,F2+G2/10000,F$2:F$5+G$2:G$5/10000)-IF(D2+E2/10000>D$2:D$5+E$2:E$5/10000,D2+E2/10000,D$2:D$5+E$2:E$5/10000))>0))>1
J2:J5	J2	=SUMPRODUCT(--(IF(F2+G2/10000<F$2:F$5+G$2:G$5/10000,F2+G2/10000,F$2:F$5+G$2:G$5/10000)-IF(D2+E2/10000>D$2:D$5+E$2:E$5/10000,D2+E2/10000,D$2:D$5+E$2:E$5/10000)>0))>1
D9	D9	=MAX(0,MIN(B9,B10)-MAX(A9,A10))
Press CTRL+SHIFT+ENTER to enter array formulas.

The Control+Shift+Enter is required for it. The SUMPRODUCT can be written in such a way that it is not required, but it gets even uglier.

Toadstool raises an excellent point that the formula, whichever version, would be simpler if the miles and yards were combined into a single value. Much of the complexity of my formulas is due to combining those values within the formula, instead of within a helper cell.

 

[MrAStanley]

Hi @Eric W and @Toadstool,

I truly appreciate both of your responses. I will take a look at these shortly.

I just wanted to unpack your responses.

The main issue you mentioned (having miles and yards) will be resolved by the following example below:

The GREEN, is the original data, The BLUE is the concatenated version with 'm' and 'yd' added (the one I'd use to view the mileages more easily), and the YELLOW is concatenated also, and is what I would be using the actual formula on.

This way I believe it would be much easier for the formula to do the work and also from a readability standpoint. Apologies if it looks confusing.

I think the main issue here is going to be finding the 2 start and 2 end values for the method as you have mentioned @Eric W, as these files could have any given mileage at various different places.

I have just placed them out in a much more readable, numerical order in my previous examples.

I'd imagine I would just need to locate the lowest and highest mileage and add them in as the Start and End values - no problem.



@Toadstool, your approach appears to me, to be better suited for what we need. I just can't fully grasp it yet though We just need to identify these overlaps by any means necessary at this point.

Hopefully, having the concatenate example above will make things easier, but I appreciate this isn't a straight forward request. You can just decimal values if that helps?

I will keep going on my quest to find a solution but please do feel free to have another go with the example above to see if it makes things easier.

I can't seem to attach anything on here so the inserted JPG is all I can do. so, so sorry

Thanks again, I hope to hear back from you with or without a solution.

Kind regards
Andrew. S

 

[Toadstool]

Andrew,

Mile & Yards Representation
I don't think you want to use that yellow example as 0.1 isn't really representative of 0m 1yd. How would you represent 880 yards which is half a mile? If you use the true representation of 1 yard as a fraction then it's 0.000568182 miles, which if you round to 2 digits would disappear.

I strongly recommend you stay with yards as the internal working representation as those columns could be hidden but the formulae would still work.

If you want to use the blue format as your input (e.g. "27m 880yd") then that works but it becomes a text field which Excel doesn't understand. You can reformat so Excel can work with it but it's a little messy. See columns G and H in my new example below.

Overlap Calculations
You don't say how many rows of data you have and my example just calculates down to row 10 but is easily changed.

I don't know the maximum number of overlaps which may occur. My previous example allowed for 6 but here I'm lowering that to 3 so I can offer more solutions.

This new format uses the previous calculation for columns J, K and L to identify which rows of data contain an overlap.
I've added 3 columns M, N and O to use the MAX MIN MAX approach to actually calculate how many yards overlap.
I've added columns P, Q and R to present that overlap yardage in your preferred format of m and yd.

I look forward to your feedback.

MrAStanley.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ELR	TID		Start	End		Start Yards	End Yards		Overlapped Rows			Overlap Yards			Overlap m yd
2	X	X		0m 1yd	10m 0yd		1	17600		3	6	7	1760	3520	15180	1m 0yd	2m 0yd	8m 1100yd
3	X	X		9m 0yd	11m 0yd		15840	19360		2	6	7	1760	3520	3520	1m 0yd	2m 0yd	2m 0yd
4	X	X		11m 0yd	12m 0yd		19360	21120		6	7		400	1760		0m 400yd	1m 0yd
5	X	X		12m 0yd	13m 0yd		21120	22880		7			1760			1m 0yd
6	X	X		8m 0yd	11m 400yd		14080	19760		2	3	4	3520	3520	400	2m 0yd	2m 0yd	0m 400yd
7	X	X		1m 660yd	22m 0yd		2420	38720		2	3	4	15180	3520	1760	8m 1100yd	2m 0yd	1m 0yd
8	X	X		22m 0yd	22m 600yd		38720	39320
9	X	X		23m 0yd	27m 880yd		40480	48400		10			1000			0m 1000yd
10	X	X		25m 0yd	25m 1000yd		44000	45000		9			1000			0m 1000yd
11
3rd

Cell Formulas
Range	Formula
G2:H10	G2	=(LEFT(D2,SEARCH("m",D2)-1)*1760)+(SUBSTITUTE(MID(D2,SEARCH("m",D2)+1,99),"yd",""))
J2:L10	J2	=IFERROR(AGGREGATE(15,6,ROW($G$2:$G$10)-ROW($G$1)/(((($G2>$G$2:$G$10)*($G2<$H$2:$H$10))+(($G2<$G$2:$G$10)*($H2>$G$2:$G$10))+(($G2>$G$2:$G$10)*($H2<$H$2:$H$10))+(($G2<$G$2:$G$10)*($H2>$H$2:$H$10))>0)),COLUMN()-COLUMN($I$2))+ROW($J$1),"")
M2:O10	M2	=IF(J2="","",MAX(MIN(($H2,INDEX($H$2:$H$10,J2-1)))-MAX($G2,INDEX($G$2:$G$10,J2-1),0)))
P2:R10	P2	=IF(J2="","",INT(M2/1760)&"m "&MOD(M2,1760)&"yd")

 

[Eric W]

Using Toadstool's layout, here's how you could do it with Excel 365. The SPILL functionality makes it a little cleaner.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ELR	TID		Start	End		Start Yards	End Yards		Overlapped Rows
2	X	X		0m 1yd	10m 0yd		1	17600		3	6	7
3	X	X		9m 0yd	11m 0yd		15840	19360		2	6	7
4	X	X		11m 0yd	12m 0yd		19360	21120		6	7
5	X	X		12m 0yd	13m 0yd		21120	22880		7
6	X	X		8m 0yd	11m 400yd		14080	19760		2	3	4	7
7	X	X		1m 660yd	22m 0yd		2420	38720		2	3	4	5	6
8	X	X		22m 0yd	22m 600yd		38720	39320
9	X	X		23m 0yd	27m 880yd		40480	48400		10
10	X	X		25m 0yd	25m 1000yd		44000	45000		9
Sheet20

Cell Formulas
Range	Formula
G2:H10	G2	=(LEFT(D2,SEARCH("m",D2)-1)*1760)+(SUBSTITUTE(MID(D2,SEARCH("m",D2)+1,99),"yd",""))
J7:N7,J6:M6,J5,J8:J10,J4:K4,J2:L3	J2	=IFERROR(TRANSPOSE(FILTER(SEQUENCE(ROWS($A$2:$A$10))+ROW($A$2)-1,(($G$2:$G$10>G2)*($G$2:$G$10<H2))+((G2>$G$2:$G$10)*(G2<$H$2:$H$10)))),"")
Dynamic array formulas.

Also, as far as pasting images into a post, there is a tool called XL2BB which Toadstool and I just used. See the link in my signature or the reply box. It's easy to download, install, and use.

 

[BillDW]

Hi Guys,

I am really hoping somebody can help me with this as I have tried everything but still cannot find a viable solution.

My goal is to be able to identify overlapping mileage (miles and yards) from within my excel file.

Please see below an example of the data...

View attachment 53721

(Please ignore the ELR and TID values for now)

I would like to be able to "flag up" rows which have overlapping mileage. As you can see, one start value would be 'Miles' and 'Yards' together (0.1) and the end would also be 'Miles' and 'Yards' together (10.0).

I figured that a Concatenate formula would get these together to form a more manageable value which is fine. However, I simply cannot find a solution to the initial problem of being able identify the overlapping data ranges.

We have an overlap here in the first 2 rows, as 9.0 - 11.0 also overlaps with the row above 0.1 - 10.0. This is one mile of overlap.

Another example image which may better explain the overlap issue can be seen here...

Is there is formula that can be used to achieve this outcome? I've never really been any good at Excel so I'm really hoping that an expert here can help me out.

Hopefully this all makes sense. The ELR and TID we can check by eye at this stage, I just need to get the overlapping mileages to highlight, in order to avoid overlapping sections.

Thank you in advance.

Andrew.

You didn't say whether it's acceptable to sort your data. Based on your description of the problem I'm assuming that sorting isn't an option, but just in case it is - I think this could become a much simpler problem if you are only wanting to flag overlaps.

First, I agree with everyone else that your data first needs to be converted to a single unit (miles, feet, or yards) - not concatenated. Converting to yards is simple enough, and I'll use Toadstool's sample data here:

Note that I've sorted the data by column I (Start Yards). In this way, any overlaps can be identified if the End Yards for one row is greater than the Start Yards for the next row. For non-sorted data, you'll need to use something like what has been previously described, but if sorted I believe this will work.

The 1 and 0 values in column K don't represent the number of overlaps - simply whether a range overlaps the next row. A "1" means it does overlap, and a "0" means it does not. Note that it doesn't work in reverse (row 5 overlaps the previous row but isn't flagged). That shouldn't be a problem since those overlaps are still flagged in the preceding row.

This is my first post and I'm a bit nervous that I've oversimplified things (or missed something). Obviously this is a very elementary solution and I suspect you'd have used it if it were applicable, but I've used this method a lot when I just need to check for overlaps and can manually correct them.