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

kashifjillani · Feb 11, 2012

Hello experts,

Tried all methods without success

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)

Any clue how to fix it?

e.g.
<table border="0" cellpadding="0" cellspacing="0" width="531"><tbody><tr style="height:12.75pt" height="17"><td style="height:12.75pt;width:48pt" height="17" width="64"> <table border="0" cellpadding="0" cellspacing="0" width="960"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3291; width:68pt" span="2" width="90"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="2" width="70"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">ASSETID</td> <td class="xl24" style="width:53pt" width="70">LENGTH</td> <td class="xl24" style="width:68pt" width="90">US_INVERT</td> <td class="xl24" style="width:68pt" width="90">DS_INVERT</td> <td class="xl24" style="width:58pt" width="77">PIPE_DIA</td> <td class="xl24" style="width:53pt" width="70">US_TAG</td> <td class="xl24" style="width:53pt" width="70">DS_TAG</td> <td style="width:48pt" width="64">TYPEID</td> <td style="width:48pt" width="64">PIPEMATERI</td> <td style="width:74pt" width="99">US_PIT_NAM</td> <td style="width:48pt" width="64">TYPEDESCRI</td> <td style="width:104pt" width="138">ASSETDESCR</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196934</td> <td align="right">11.13</td> <td align="right">37.46</td> <td align="right">37.34</td> <td align="right">300</td> <td align="right">7798</td> <td align="right">7797</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">17</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7798 - 7797</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196935</td> <td align="right">5.29</td> <td align="right">37.34</td> <td align="right">37.32</td> <td align="right">300</td> <td align="right">7797</td> <td align="right">7722</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7797 - 7722</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196936</td> <td align="right">12.95</td> <td align="right">40.25</td> <td align="right">40.2</td> <td align="right">300</td> <td align="right">7642</td> <td align="right">7641</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7642 - 7641</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196937</td> <td align="right">25.93</td> <td align="right">39.61</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7821</td> <td align="right">7846</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>D</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7821 - 7846</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196938</td> <td align="right">10.45</td> <td align="right">40.61</td> <td align="right">40.46</td> <td align="right">375</td> <td align="right">7783</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">22</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7783 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196939</td> <td align="right">76.51</td> <td align="right">0</td> <td align="right">40.42</td> <td align="right">375</td> <td align="right">7772</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">2</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7772 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196940</td> <td align="right">8.26</td> <td align="right">39.88</td> <td align="right">39.84</td> <td align="right">300</td> <td align="right">7735</td> <td align="right">7734</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7735 - 7734</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196941</td> <td align="right">10.11</td> <td align="right">49.48</td> <td align="right">49.42</td> <td align="right">300</td> <td align="right">8023</td> <td align="right">8022</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td>SE</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 8023 - 8022</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196942</td> <td align="right">7.41</td> <td align="right">41.56</td> <td align="right">41.47</td> <td align="right">300</td> <td align="right">7710</td> <td align="right">7709</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7710 - 7709</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196943</td> <td align="right">9.54</td> <td align="right">0</td> <td align="right">40.62</td> <td align="right">300</td> <td align="right">7659</td> <td align="right">7658</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">40</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7659 - 7658</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196944</td> <td align="right">28.87</td> <td align="right">46.64</td> <td align="right">0</td> <td align="right">450</td> <td align="right">70108</td> <td align="right">8004</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">28</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 70108 - 8004</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196945</td> <td align="right">37.83</td> <td align="right">40.96</td> <td align="right">40.66</td> <td align="right">375</td> <td align="right">7912</td> <td align="right">7911</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7912 - 7911</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196946</td> <td align="right">21.53</td> <td align="right">40.28</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7645</td> <td align="right">7644</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">23</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7645 - 7644</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196947</td> <td align="right">48.5</td> <td align="right">38.99</td> <td align="right">38.3</td> <td align="right">450</td> <td align="right">7907</td> <td align="right">7906</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">6</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7907 - 7906</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196948</td> <td align="right">80.65</td> <td align="right">0</td> <td align="right">39.88</td> <td align="right">375</td> <td align="right">7823</td> <td align="right">7822</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>F</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7823 - 7822</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196949</td> <td align="right">37.62</td> <td align="right">39.19</td> <td align="right">0</td> <td align="right">450</td> <td align="right">7633</td> <td align="right">7632</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">8</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7633 - 7632</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196950</td> <td align="right">18.29</td> <td align="right">0</td> <td align="right">40.28</td> <td align="right">375</td> <td align="right">7881</td> <td align="right">7880</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">5</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7881 - 7880</td> </tr> </tbody></table></td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:58pt" width="77">
</td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:53pt" width="70">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr></tbody></table>

Can send the table if required

Thanks in advance,

regards,

Kashif

kashifjillani · Feb 18, 2012

kashifjillani said:
Hi p45cal,

Sorry couldnt find your name,

Thanks for your effort,

You are right, in my previous post it was stated that both ways are valid (either by trigonometry or arithmetic as you said)

Yes, we can put value of DS-TAG of AssetID 7850 ( 13.97 ) straightaway, because the TAGs are same.

However, it is not the typical case, if a tag (manhole) was not accessed, we wouldnt have any value. May be something special about this case (other pipe fully immersed that couldnt be surveyed). BUT, yet we can take this value anyway.

The main challenge is automation of these calculations, as you can see from the size of data, manual calculations for each are next to impossible!

ANOTHER, WAY I CAN SEE IS (just clicked):

I HAVE GOT GROUND SURFACE ELEVATION OF EACH TAG,

IF WE CAN DRAW GRAPHS OF CONNECTING TAGS (SURFACE ELEVATIONS AND PIPE INVERTS). THEN, FROM THE GRAPH WE CAN DRAG THE VALUES TO MATCH THE UPSTREAM AND DWONSTREAM TREND.

SO, WE NEED TO FIND THE ASSETS AND ARRANGE IN AN ORDER, US_TAG DS_TAG OF ONE ASSET THEN US_TAG DS_TAG OF NEXT AND SO ON UNTIL WE FIND NO DS_TAG VALUE AS MAIN/MW/OPEN DRAIN/OUTLET OR SOMETHING

THEN WE CAN FIX ALL THE 0 VALUES.

IT MIGHT NEED 20+ GRAPHS WITH TWO LINES IN EACH, (SURFACE ELEVATION LINE AND US_INVERT OR DS_INVERT)

TAGS AGAINST SURFACE ELEVATION VALUES ARE UPLOADED HERE

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

Note: The whole drainage network is not just one interconnected network but there are 20+ standalone networks.

I HOPE YOU GET THE IDEA.

THANKS AGAIN, AND I REALLY ACKNOWLEDGE YOUR EFFORT

I am not sure if we can play with lines in a graph in a manner that table values are also changed accordingly.

kashifjillani · Feb 19, 2012

Hello all who attempted to solve this "mystery" problem,

It seems there are many issues which prevent reaching a sure shot solution

Another approach, which is a bit simple but require human intervention is: to draw a graph and manually adjust the missing data to match the trend of available data.

But, the data needs to be sorted link wise as shown in spreadsheet

http://www.mediafire.com/?2tqu3ovmxcqqced

You would see data in raw form (as copied from gis layer) and sorted (as required for adjustment)

Any, clue how to sort this data from two different layers

Regards,

p45cal · Feb 19, 2012

kashifjillani said:
It seems there are many issues which prevent reaching a sure shot solution

I've been looking at the data and it's got plenty of problem data. We'll look at how to deal with that later.

kashifjillani said:
But, the data needs to be sorted link wise as shown in spreadsheet

Well, the data seems to be treatable, more or less, as a linked list (or more accurately several linked lists). Linked lists is/was actually programming terminology for a way of sorting data without moving it about; just add a link to the next record in the form of a record no., then you can move through the records in order by jumping from one record to another using the added link(s) for each record.
In this case the links are already there in the form of downstrean and upswtream tags, it's just a matter of following them. Take any asset, look at its downstream tag, look for that tag amongst the upstream tags in the list, when you've found it, look at its downstream tag, look for that in the upstream tags. etc. etc. Of course, because more than one asset may have the same upstream tag this means there's a branch into more directions.

Anyway, in preparation to do this I thought I'd check the integrity of the data to see that no asset had the same upstream and downstream tag; 3 had:

Excel Workbook

B

C

D

E

F

G

2

ASSETID

LENGTH

US_INVERT

DS_INVERT

US_TAG

DS_TAG

2361

199313

52.66

0

3303

4513

204287

51.3

18.72

18.34

916

5597

215158

29.66

27.85

27.65

2556

Sheet

Is this normal? A pipe of 52 metres starting and finishing in the same place?

p45cal · Feb 19, 2012

p45cal said:
I've been looking at the data and it's got plenty of problem data. We'll look at how to deal with that later.

How to deal with this?:
Below is a selection of some of the anomalies. I've highlighted in grey those tags which are the same and the corresponding us/ds tag. You'd expect the values to be more or less the same. I've highlighted in red the ones that seem at variance, though you can't always be sure which is the value at variance.
These values are potentially to be used to calculate interpolated heights.
In yellow, it looks like two are at variance! How do you tell the computer this?!
In purple, not sure which one is wrong.

[I'll have a look at getting assets sorted according to the links tomorow or the day after (I may not get time). This might shed light on which values are more likely to be correct.]

Excel Workbook

A

B

C

D

E

F

G

24

row no.

ASSETID

LENGTH

US_INVERT

DS_INVERT

US_TAG

DS_TAG

Sheet

Excel Workbook

A

B

C

D

E

F

G

25

7590

219312

16.41

17.22

0

8262

8261

26

7726

219448

1.05

18.08

1804

70172

8262

27

7857

219579

59.3

17.8

17.27

8263

8262

28

29

7733

219455

62.35

998

997

8312

8311

30

7829

219551

66.2

0

19495

8312

31

7837

219559

5.04

14.02

13.97

8333

8312

32

33

7579

219301

11.67

16.5

16.44

4020

4019

34

7672

219394

5.31

0

997

8275

4020

35

36

7733

219455

62.35

998

997

8312

8311

37

7739

219461

4.31

13.35

13.3

8311

4012

38

7820

219542

4.82

14.16

13.3

19508

8311

39

40

7675

219397

67.51

15.54

14.87

8259

8258

41

7759

219481

57.85

998

997

8258

8256

42

7783

219505

5.71

0

15.24

8305

8258

43

44

7741

219463

66.55

14.43

0

8256

4013

45

7759

219481

57.85

998

997

8258

8256

46

7781

219503

3.92

14.63

14.46

8302

8256

47

7879

219601

1.87

15.13

15.11

8244

8256

48

49

4406

204180

37.08

16.89

0

1830

1829

50

4575

204349

149.39

303.91

0

1829

1828

51

4704

204478

64.77

17.62

16.81

1824

1829

52

53

4573

204347

23.83

303.91

303.61

1827

4211

54

4800

204574

48.29

14.98

14.85

4212

4211

55

4802

204576

13.39

14.76

0

4211

4210

56

57

4637

204411

20.01

0

303.61

1874

4222

58

4711

204485

7.71

15.87

15.63

4223

4222

59

4713

204487

27.76

15.63

15.58

4222

4221

60

61

4576

204350

29.1

303.91

303.61

1881

1880

62

4611

204385

37.49

16.99

303.61

1882

1881

63

4867

204641

14.22

17.66

17.49

1887

1881

64

65

8592

248261

8.13

13.84

13.75

296

287

66

9450

253262

67.1

-272.39

0

287

286

67

9498

253524

81.86

0

13.75

288

287

68

69

214

197150

80.57

303.61

0

7924

7923

70

266

197205

75.99

303.61

303.3

7925

7924

71

300

197239

42.53

0

38.25

7926

7924

72

73

7254

217234

73.11

15.93

15.71

1392

1391

74

7255

217235

38.46

0

303.61

4758

1392

75

7322

217336

42.94

0

16

1398

1392

76

7463

217498

23.31

16.31

16.15

1393

1392

77

78

3689

200641

88.04

0

88.21

5000

4999

79

3695

200647

39.75

30.77

30.56

4999

4998

80

81

5760

215321

16.24

66.91

66.19

2276

2268

82

5761

215322

32.59

19.51

19.29

2268

2267

83

6010

215571

45.8

21.51

20.17

2269

2268

84

85

60

196992

11.68

0

41.13

7845

71106

86

204

197140

48.29

-0.59

71105

71106

87

292

197231

92.64

0

71106

7847

Sheet

Excel Workbook

89

5060

205608

37.34

42.23

42.06

20529

20528

90

5172

205720

44.28

42.55

42.23

20405

20529

91

5284

205832

24.85

-0.59

20530

20529

92

93

9760

261351

4.05

0

12283

12253

94

9761

261352

14.06

37.94

0

12253

12277

95

9775

261383

3.87

0

7.51

12254

12253

96

97

4880

204654

111.89

24.18

22.58

579

478

98

9272

252275

40.87

12.95

12.67

479

478

99

9448

253256

66.81

12.6

12.34

478

477

100

101

1899

198850

28.41

46.58

46.09

6894

12284

102

9769

261368

33.03

37.38

37.47

12284

12286

103

104

1168

198117

2.82

30.95

30.81

6732

6

105

3942

200894

43.9

22.43

0

6

4

106

107

3995

200947

14.73

20.78

15.24

2367

2366

108

4242

201194

14.09

20.78

20.63

2366

2365

Sheet

kashifjillani · Feb 20, 2012

Thanks p45cal,

You are right, the data has errors, off course these are human input.

Thats why, the fully automated methods wont work.

As in my last post, I picked up a network manually to work on it.

This way is laboursome, but we have no choice.

If there is any way to sort the data, I am ready to pick the data and keep apply certain command which would just sort the data for me.

And then, one can fix it graphically (also check for corrections simultaneously)

Regards,

p45cal · Feb 20, 2012

kashifjillani said:
But, the data needs to be sorted link wise as shown in spreadsheet < snip >
Any, clue how to sort this data from two different layers

Yes, using the linked list method alluded to earlier. Using only the data in DrainagePipes.xls I got the same sequence as in your AdjustingDataGraphical example.
This bit of (1st draft) code, which you should run after selecting a cell in column F, will try and follow the pipe sequence in one direction and select the cell in column F that is the end of the sequence:

Code:

Sub findaroot()
Set c = ActiveCell  'cell in colm F
Do
  Set c = Range("G:G").Find(what:=c.Value, LookIn:=xlValues, lookat:=xlWhole)  '.Offset(, -1)
  If Not c Is Nothing Then Set c = c.Offset(, -1): c.Select
Loop Until c Is Nothing
End Sub

This next bit of code goes in the opposite direction and does the same, but does a little copying en route:

Code:

Sub findanend([COLOR=Blue]destnws[/COLOR])
Set c = ActiveCell  'cell in colm F
Cells(c.Row, "B").Resize(, 6).Copy destnws.Cells(Rows.Count, "B").End(xlUp).Offset(1)
Do
  Set c = Range("F:F").Find(what:=c.Offset(, 1).Value, LookIn:=xlValues, lookat:=xlWhole)  '.Offset(, -1)
  If Not c Is Nothing Then
    c.Select
    Cells(c.Row, "B").Resize(, 6).Copy destnws.Cells(Rows.Count, "B").End(xlUp).Offset(1)
  End If
Loop Until c Is Nothing
End Sub

It shouldn't be run on its own, but called with an argument, being the destination worksheet for copying to. This code too depends on the selected cell in column F as a starting point.

So if we run these one after the other, the first will find one end of a sequence, then the other will use that as a starting point to run the other way to the other end:

Code:

Sub findasequence()
Set SrcWS = ActiveSheet
findaroot
Set destnws = Sheets.Add(after:=Sheets(Sheets.Count))
SrcWS.Activate
findanend destnws
End Sub

Again, this needs to be run after selecting a cell in column F.

So to get the sequence you had in AdjustingDataGraphical, I first searched for one of the pipe ASSETIDs, in DrainagePipes column B, selected the cell in column F of the same row and ran findasequence, and got:

Excel Workbook

B

C

D

E

F

G

1

ASSETID

LENGTH

US_INVERT

DS_INVERT

US_TAG

DS_TAG

2

205684

9.03

0

18569

20378

3

205698

67.95

0

50.72

20378

18570

4

205713

12.16

50.7

51.01

18570

18571

5

205714

48.89

51.01

0

18571

20377

6

205721

90.85

0

50.02

20377

20376

7

205740

11.42

50

50.06

20376

20374

8

205718

5.1

50.02

0

20374

20369

9

205637

22.87

0

20369

20368

10

205696

22.62

0

20368

20367

11

205703

21.71

0

20367

0

Sheet

You could use the same logic to sort your smaller sequences in AdjustingDataGraphical.

[By the way, when using the code on DrainagePipes, currently it doesn't handle branching because it always uses the first instance of a given tag that it finds. I'm not even sure I'm searching in the right direction.]

In the results above, you'll have a hard job interpolating anything but one value. On exploration, this happens quite a lot.
You've put a chart in AdjustingDataGraphical, but this takes no account of distance/pipe length. You need to use an XY scatter, and have distance at the bottom (x-axis). Here's another sequence I found at random:

Excel Workbook

B

C

D

E

F

G

1

ASSETID

LENGTH

US_INVERT

DS_INVERT

US_TAG

DS_TAG

2

197411

22.8

42.44

42.06

7715

7711

3

197052

45.93

41.99

41.6

7711

7710

4

196942

7.41

41.56

41.47

7710

7709

5

196964

38.55

41.46

41.2

7709

7674

6

197002

1.23

41.2

41.18

7674

7708

7

197056

39.27

40.92

0

7708

7707

8

197253

8.34

0

7707

7677

9

197134

72.67

0

39.34

7677

7727

10

197181

5.01

0

7727

7726

11

197381

68.72

37.52

37.43

7726

7725

12

197280

30.81

37.38

36.81

7725

7724

13

197187

44.31

0

7724

68714

14

197496

31.8

0

36.57

68714

68713

15

197220

80.04

36.57

0

68713

7723

16

197174

88.54

36.4

36.27

7723

7722

17

197287

37.71

36.08

0

7722

7902

18

197316

4.24

0

7902

69813

19

197067

12.28

0

69813

7470

20

197077

3.28

0

36.02

7470

0

Sheet

I then worked out cumulative distance, and got:

Excel Workbook

I

J

1

Dist

invert

2

0

42.44

3

22.8

41.99

4

22.8

42.06

5

68.73

41.56

6

68.73

41.6

7

76.14

41.46

8

76.14

41.47

9

114.69

41.2

10

114.69

41.2

11

115.92

40.92

12

115.92

41.18

13

155.19

0

14

155.19

0

15

163.53

0

16

163.53

0

17

236.2

0

18

236.2

39.34

19

241.21

37.52

20

241.21

0

21

309.93

37.38

22

309.93

37.43

23

340.74

0

24

340.74

36.81

25

385.05

0

26

385.05

0

27

416.85

36.57

28

416.85

36.57

29

496.89

36.4

30

496.89

0

31

585.43

36.08

32

585.43

36.27

33

623.14

0

34

623.14

0

35

627.38

0

36

627.38

0

37

639.66

0

38

639.66

0

39

642.94

0

40

642.94

36.02

Sheet

which I then charted on xy scatter (the zeroes are plotted off chart):
<embed src="https://www.box.com/embed/27lqpk2quazbh98.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

Sometimes you'll see two red data points almost on top of each other, this is two heights in the same manhole.

In xl2003 and xl2010 (but not in xl2007 unless an Office update has reintroduced it) you can select a single data point on a line in a chart (click it once, pause, click it again) and drag it, either vertically or horizontally, but not both at the same time, and drop it where you want it. The corresponding value in the sheet will adjust.

Only pointers, hopefully you'll be able to use some of them?

kashifjillani · Feb 22, 2012

p45cal said:
Yes, using the linked list method alluded to earlier. Using only the data in DrainagePipes.xls I got the same sequence as in your AdjustingDataGraphical example.
This bit of (1st draft) code, which you should run after selecting a cell in column F, will try and follow the pipe sequence in one direction and select the cell in column F that is the end of the sequence:

Code:

Sub findaroot() Set c = ActiveCell 'cell in colm F Do Set c = Range("G:G").Find(what:=c.Value, LookIn:=xlValues, lookat:=xlWhole) '.Offset(, -1) If Not c Is Nothing Then Set c = c.Offset(, -1): c.Select Loop Until c Is Nothing End Sub

This next bit of code goes in the opposite direction and does the same, but does a little copying en route:

Code:

Sub findanend([COLOR=Blue]destnws[/COLOR]) Set c = ActiveCell 'cell in colm F Cells(c.Row, "B").Resize(, 6).Copy destnws.Cells(Rows.Count, "B").End(xlUp).Offset(1) Do Set c = Range("F:F").Find(what:=c.Offset(, 1).Value, LookIn:=xlValues, lookat:=xlWhole) '.Offset(, -1) If Not c Is Nothing Then c.Select Cells(c.Row, "B").Resize(, 6).Copy destnws.Cells(Rows.Count, "B").End(xlUp).Offset(1) End If Loop Until c Is Nothing End Sub

It shouldn't be run on its own, but called with an argument, being the destination worksheet for copying to. This code too depends on the selected cell in column F as a starting point.

So if we run these one after the other, the first will find one end of a sequence, then the other will use that as a starting point to run the other way to the other end:

Code:

Sub findasequence() Set SrcWS = ActiveSheet findaroot Set destnws = Sheets.Add(after:=Sheets(Sheets.Count)) SrcWS.Activate findanend destnws End Sub

Again, this needs to be run after selecting a cell in column F.

So to get the sequence you had in AdjustingDataGraphical, I first searched for one of the pipe ASSETIDs, in DrainagePipes column B, selected the cell in column F of the same row and ran findasequence, and got:
Excel Workbook
B C D E F G
1 ASSETID LENGTH US_INVERT DS_INVERT US_TAG DS_TAG
2 205684 9.03 0 0 18569 20378
3 205698 67.95 0 50.72 20378 18570
4 205713 12.16 50.7 51.01 18570 18571
5 205714 48.89 51.01 0 18571 20377
6 205721 90.85 0 50.02 20377 20376
7 205740 11.42 50 50.06 20376 20374
8 205718 5.1 50.02 0 20374 20369
9 205637 22.87 0 0 20369 20368
10 205696 22.62 0 0 20368 20367
11 205703 21.71 0 0 20367 0
Sheet

You could use the same logic to sort your smaller sequences in AdjustingDataGraphical.

[By the way, when using the code on DrainagePipes, currently it doesn't handle branching because it always uses the first instance of a given tag that it finds. I'm not even sure I'm searching in the right direction.]

In the results above, you'll have a hard job interpolating anything but one value. On exploration, this happens quite a lot.
You've put a chart in AdjustingDataGraphical, but this takes no account of distance/pipe length. You need to use an XY scatter, and have distance at the bottom (x-axis). Here's another sequence I found at random:
Excel Workbook
B C D E F G
1 ASSETID LENGTH US_INVERT DS_INVERT US_TAG DS_TAG
2 197411 22.8 42.44 42.06 7715 7711
3 197052 45.93 41.99 41.6 7711 7710
4 196942 7.41 41.56 41.47 7710 7709
5 196964 38.55 41.46 41.2 7709 7674
6 197002 1.23 41.2 41.18 7674 7708
7 197056 39.27 40.92 0 7708 7707
8 197253 8.34 0 0 7707 7677
9 197134 72.67 0 39.34 7677 7727
10 197181 5.01 0 0 7727 7726
11 197381 68.72 37.52 37.43 7726 7725
12 197280 30.81 37.38 36.81 7725 7724
13 197187 44.31 0 0 7724 68714
14 197496 31.8 0 36.57 68714 68713
15 197220 80.04 36.57 0 68713 7723
16 197174 88.54 36.4 36.27 7723 7722
17 197287 37.71 36.08 0 7722 7902
18 197316 4.24 0 0 7902 69813
19 197067 12.28 0 0 69813 7470
20 197077 3.28 0 36.02 7470 0
Sheet

I then worked out cumulative distance, and got:
Excel Workbook
I J
1 Dist invert
2 0 42.44
3 22.8 41.99
4 22.8 42.06
5 68.73 41.56
6 68.73 41.6
7 76.14 41.46
8 76.14 41.47
9 114.69 41.2
10 114.69 41.2
11 115.92 40.92
12 115.92 41.18
13 155.19 0
14 155.19 0
15 163.53 0
16 163.53 0
17 236.2 0
18 236.2 39.34
19 241.21 37.52
20 241.21 0
21 309.93 37.38
22 309.93 37.43
23 340.74 0
24 340.74 36.81
25 385.05 0
26 385.05 0
27 416.85 36.57
28 416.85 36.57
29 496.89 36.4
30 496.89 0
31 585.43 36.08
32 585.43 36.27
33 623.14 0
34 623.14 0
35 627.38 0
36 627.38 0
37 639.66 0
38 639.66 0
39 642.94 0
40 642.94 36.02
Sheet

which I then charted on xy scatter (the zeroes are plotted off chart):
<embed src="https://www.box.com/embed/27lqpk2quazbh98.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

Sometimes you'll see two red data points almost on top of each other, this is two heights in the same manhole.

In xl2003 and xl2010 (but not in xl2007 unless an Office update has reintroduced it) you can select a single data point on a line in a chart (click it once, pause, click it again) and drag it, either vertically or horizontally, but not both at the same time, and drop it where you want it. The corresponding value in the sheet will adjust.

Only pointers, hopefully you'll be able to use some of them?

Hi p45cal,

Thank you v much to figure it out

This is exactly what required; this way, erroneous data can also be verified - adjusted.

I tried to run the VB code (Tools - Macro - VB Editor) in MS Excel 2003, but no success, can you please describe how you run it?

Also, in Sheet1 of AdjustingDataGraphical.xls you would notice another data (Tag Key and Surface-Level) which is also to be plotted on same graph - which would allow to check the ground surface level (an extra measure to maintain the proportion)

Thanks you again, waiting for your reply...

Best regards,

p45cal · Feb 23, 2012

kashifjillani said:
I tried to run the VB code (Tools - Macro - VB Editor) in MS Excel 2003, but no success, can you please describe how you run it?

Do points 1 and 2 here, and in step 3 paste the code I supplied. Now back on the sheet, select a cell in column F, then in get te macro list up (Alt+F8 is the shortcut) run findasequence.

kashifjillani said:
Also, in Sheet1 of AdjustingDataGraphical.xls you would notice another data (Tag Key and Surface-Level) which is also to be plotted on same graph - which would allow to check the ground surface level (an extra measure to maintain the proportion)

Thanks you again, waiting for your reply...

You would have to explain in layman's terms the meaning of things - I don't understand us invert ds invert tags, what they're measured from, surface levels, how it might be related to anything else, it's all greek to me.

I can try and give pointers, but that's all.

kashifjillani · Feb 24, 2012

Oh you are right...

The US_INVERT or DS_INVERT is the elevation (form the sea level) of the pipe's lower inner side.

The drainage pipes are normally buried in ground - we cant see them or measure elevation.

So, we need to open the connecting manhole (in our case TAG).

"Some" of the manholes (TAGs) couldn't be accessed, due to some reasons.

SurfaceLevel is again elevation (above sea level) of the manhole cover.

We use it, to prevent us from going above the surface level of manhole cover (as it is not possible the pipe would be above the manhole)

Hope it answered the question

Thanks for your efforts,

and glad to draw your attention - solving the problem

regards,

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

kashifjillani

New Member

kashifjillani

New Member

kashifjillani

New Member

p45cal

Well-known Member

p45cal

Well-known Member

kashifjillani

New Member

p45cal

Well-known Member

kashifjillani

New Member

p45cal

Well-known Member

kashifjillani

New Member

Similar threads

Share this page

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

New Member

New Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock