Find sum of range across 2 columns excluding overlapping values and gaps.

[gutterball]

From the following data, I need to find the total distance excluding overlapping values and gaps.

testing code.xlsx


or



ID BEGIN END TOTAL
1 12.24 13.12 0.88
2 16.24 17.31 1.07
3 21.45 22.14 0.69
4 20.6 21.46 0.86
5 16.34 18.16 1.82
6 7.9 9.47 1.57
7 0.96 2.45 1.49
8 0 0.98 0.98
9 4.01 5.37 1.36
10 22.13 23.52 1.39
11 13.08 13.86 0.78
12 9.5 10.6 1.1
13 10.52 12.26 1.74


15.73 total distance covered by each section

12.26 actual distance covered (do not know formula to get answer, but should be 12.26)

I used the END distance minus the BEGIN distance to figure out TOTAL, then took those summed to get "total miles covered by each section"

But I need to exclude overlapping distances and account for gaps to figure out "actual distance covered"

It does not have to be a formula or function, I can handle macros or VBA too.

[Vikas_Gautam]

what is meant by overlapping distance and gaps...?
if you can explain...


Regards
Vikas

[Bernie Deitrick]

Nope - there is no way to come up with 12.26, applying the logic that you describe. Let's sort your data by the BEGIN value:

Then let's modify the END Values to prevent overlap - if the end is greater than the beginning of the next leg, use that as the END value instead:

Please Login or Register  to view this content.

Clearly, not 12.26. So let's remove any overlaps that exists, to get lengths that are traveled only once, modifying both the BEGIN and END to remove any existing overlap.

Still not 12.26......

[gutterball]

For example, if I was taking a survey of a road, the first survey included from milepost 2 to 5, the second survey included milepost 3 to 7, the third included milepost 10 to 15, and the last included milepost 4 to 6. The total miles of surveyed road would be 10 miles, and the total miles of survey data would be 14



example.xlsx

or

SURVEY BEGIN END TOTAL
1 2 5 3
2 3 7 4
3 10 15 5
4 4 6 2
total survey miles 14
miles of road surveyed 10




What formula, macro or VBA could I use to calculate the total miles of road surveyed?

[Bernie Deitrick]

This should work...

miles.xlsx

[gutterball]

Bernie Deitrick, you are correct, I was missing some values when I calculated the mileage, it should be 14.58.
can you put those steps into a macro? I should have put this in the macro section.

I don't know how to modify the end values of the leg, if it is greater than the beginning of the next leg.

[gutterball]

Thank you, I will try to scale this up, or create a macro.

Reputation added!

[Bernie Deitrick]

It should scale up OK - those were the same formulas I used to get 14.58. Just make sure that you sort the data, and extend the formulas correctly.

[gutterball]

It should scale up OK - those were the same formulas I used to get 14.58. Just make sure that you sort the data, and extend the formulas correctly.

Thanks Bernie

[gutterball]

Hmm, the code works unless I have a situation like this

UNIQUE BEGIN END
1 0 3.17
2 0 1.55
3 0 1.32
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0.09
12 0 0
13 0 0
14 0 0
15 0 0
16 0 0
17 0 0
18 0 0
19 0.05 0.34
20 0.13 1.32
21 0.15 0.87
22 0.21 0.24

I end up with negative values

hmmm.xlsx



UNIQUE BEGIN END begin end miles
1 0 3.17 0 3.17 3.17
2 0 1.55 3.17 1.55 -1.62
3 0 1.32 1.55 1.32 -0.23
4 0 0 1.32 0 -1.32
5 0 0 0 0 0
6 0 0 0 0 0
7 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
11 0 0.09 0 0.09 0.09
12 0 0 0.09 0 -0.09
13 0 0 0 0 0
14 0 0 0 0 0
15 0 0 0 0 0
16 0 0 0 0 0
17 0 0 0 0 0
18 0 0 0 0 0
19 0.05 0.34 0.05 0.34 0.29
20 0.13 1.32 0.34 1.32 0.98
21 0.15 0.87 1.32 0.87 -0.45
22 0.21 0.24 0.87 0.24 -0.63
23 0.39 6.66 0.39 6.66 6.27
24 0.46 1.19 6.66 1.19 -5.47
25 0.85 3.04 1.19 3.04 1.85
26 1.21 1.97 3.04 1.97 -1.07
27 1.31 2.15 1.97 2.15 0.18
28 1.33 1.72 2.15 1.72 -0.43
29 1.6 1.96 1.72 1.96 0.24

[Bernie Deitrick]

Try this, with the list you have in columns A, B and C starting in Row 1 of the activesheet.

Please Login or Register  to view this content.