## 1,000 missing survey data points to be fixed by interpolation

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