Hello experts,

I have large data of around 10,000 drainage pipe segments and 10,000 junctions in ArcGIS layer

The problem is: due to some reason certain junctions could not be accessed for survey, thus 1,000+ entries are missing for pipes (up stream and d/s invert elevation) and junctions' surface elevation

Logically, interpolation should work here. (based on slope, u/s tag invert level or two junction u/s tag invert level or likewise)

Tried to do it manually in excel, but quite tiring and with less confidence (unprofessional too)

ASSETID LENGTH US_INVERT DS_INVERT US_TAG DS_TAG
*196939 76.51 0 40.42 7772 7771
*197048 34.77 42.98 0 7773 7772
196938 10.45 40.61 40.46 7783 7771
196939 76.51 0 40.42 7772 7771
196940 8.26 39.88 39.84 7735 7734
Drainage pipes data


Above, I have put * to just two rows which are involved in calculations out of the whole data from 10,062 rows (Row 3 to Row 10,064)

1. To calculate US_INVER (US_TAG) elevation we need to find an asset where 7772 is under column heading DS_TAG
2. That is ASSETID 197048
3. After finding it (through some function/formula), we have got on US_INV and one DS_INV
4. Now we want to know elevation of a point in between two known elevations: which are +42.98 and +40.42 and total distance b/w these two points is length of Asset ID 196939(76.51m) and 197048(34.77m)=111.28m
5. The point is located 34.77m downstream of +42.98 and 76.51m upstream from of +40.42
6. We have just created a right angle triangle where Perpendicular is 42.98-40.42=2.56m and Base is 34.77+76.51=111.28
7. To calculate the angle of drainage pipe tan Ѳ = 2.56/111.28, Ѳ=1.317
8. After knowing the angle of pipe we can easily know the elevation of any point along the hypotenuse
9. So, US_INVERT (US_TAG) = 42.98 - tan (1.317) * 34.77 = +42.18

Alternatively
1. After step 5. we have another choice; by dividing value of perpendicular with base and multiply with distance from upstream and subtracting from upstream elevation i.e. 42.98 - (2.56/111.28)*34.77 = +42.18 again

This is the value we require at US_INVER (US_TAG) of Asset ID 196939

And

DS_INVERT (DS_TAG) of ASSETID 197048

Also, something similar would be require to fill in the missing data under column DS_INVERT

There are around 1000 missing values, if we know the formula we Ctrl+H (find and replace “0”) to save time and effort

Also, sometime one asset upstream we wont get any value (“0”again), in that case we can go two steps up (but, if it is not possible we can leave it for manual calculation)

The main challenge here, in my understanding is finding a function which could find US_TAG value of row (in question) in a DS_TAG column and start calculating by taking values from that row and row in question.

Note: the DS_TAG value could be found in multiple rows, in that case priority should be given to a row with least value in LENGTH column. (again if it is difficult, any of row could be taken)

It seems we are pushing MSEXCEL to its limits from where C++ programming starts,

The complete file in excel format is uploaded at

http://www.mediafire.com/?uniy9cic1gvxytp

http://www.mediafire.com/?uniy9cic1gvxytp

Thanks for your time reading and efforts to solve it

Regards,

Kashif