If statement

bboysen · Jun 14, 2011

Ok all i have killed the brain today on this one and can not find anything out there on the internet that i can understand what they are trying to do and how they are coming up with solutions.

This is what i have:
A1 B1
Station Code
1220286.65 XR
1220329.70 XOB
1220342.40 XR
1220386.33 XSAG
1220403.54 XR
1220407.01 XER

OK so what i am trying to do is have an if statement that i will find the the first XR code and then find the next XR code then - the Station of them two codes to come up with a distance (first on is 44.25) all in a different cell. Is this possible with out VBA?

Steve=True · Jun 14, 2011

I used your data but got a different result as i don't think that in your example that the numbers are correct. I think that XR 1 - XR 2 = 55.75 not 44.25:

put this in cell c2

=INDEX(A2:B7,MATCH(B2,B2:B7,ROW(A2:A7)),1)-A2

bboysen · Jun 14, 2011

You are right on the total like i said my brain is fried with this today.

That work great for the areas when i do not have XR next to its self and i do apologize for not including more info. In some areas i have them right next to each other.

Like this

A B
Station Code
1220104.00 XR
1220165.05 XR
1220225.69 XR
1220286.65 XR
1220329.70 XOB
1220342.40 XR
1220386.33 XSAG
1220403.54 XR
1220407.01 XER
1220415.19 XRD
1220426.60 XER
1220445.34 XSAG
1220459.77 XCDW
1220460.73 XCDW
1220464.11 XR

Thanks for all of your help!

T. Valko · Jun 14, 2011

bboysen said:
You are right on the total like i said my brain is fried with this today.

That work great for the areas when i do not have XR next to its self and i do apologize for not including more info. In some areas i have them right next to each other.

Like this

A B
Station Code
1220104.00 XR
1220165.05 XR
1220225.69 XR
1220286.65 XR
1220329.70 XOB
1220342.40 XR
1220386.33 XSAG
1220403.54 XR
1220407.01 XER
1220415.19 XRD
1220426.60 XER
1220445.34 XSAG
1220459.77 XCDW
1220460.73 XCDW
1220464.11 XR

Thanks for all of your help!

Try this...

Book1

A

B

C

D

1

Station

Code

Difference

2

1220104.00

XR

61.05

3

1220165.05

XR

_

4

1220225.69

XR

_

5

1220286.65

XR

_

6

1220329.70

XOB

_

7

1220342.40

XR

_

8

1220386.33

XSAG

_

9

1220403.54

XR

_

10

1220407.01

XER

_

11

1220415.19

XRD

_

12

1220426.60

XER

_

13

1220445.34

XSAG

_

14

1220459.77

XCDW

_

15

1220460.73

XCDW

_

16

1220464.11

XR

_

Sheet1

Array formula** entered in D2:

=INDEX(A:A,SMALL(IF(B2:B16=C2,ROW(B2:B16)),2))-INDEX(A:A,MATCH(C2,B:B,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

bboysen · Jun 14, 2011

Try this...

Sheet1

A B C D
1 Station Code Code Difference
2 1220104.00 XR XR 61.05
3 1220165.05 XR _ _
4 1220225.69 XR _ _
5 1220286.65 XR _ _
6 1220329.70 XOB _ _
7 1220342.40 XR _ _
8 1220386.33 XSAG _ _
9 1220403.54 XR _ _
10 1220407.01 XER _ _
11 1220415.19 XRD _ _
12 1220426.60 XER _ _
13 1220445.34 XSAG _ _
14 1220459.77 XCDW _ _
15 1220460.73 XCDW _ _
16 1220464.11 XR _ _

Array formula** entered in D2:

=INDEX(A:A,SMALL(IF(B2:B16=C2,ROW(B2:B16)),2))-INDEX(A:A,MATCH(C2,B:B,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

So this is what i am getting!

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:88px;" /><col style="width:88px;" /><col style="width:175px;" /><col style="width:173px;" /><col style="width:88px;" /><col style="width:88px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">Station</td><td style="text-align:center; ">Code</td><td style="text-align:center; ">Code</td><td style="text-align:center; ">Difference with Formaula</td><td >*</td><td style="text-align:center; ">Real Difference</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">1220225.686</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">60.96368042</td><td >*</td><td style="text-align:center; ">-60.96368042</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">1220286.65</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">116.7105735</td><td >*</td><td style="text-align:center; ">-55.74689307</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">1220329.702</td><td style="text-align:center; ">XOB</td><td style="text-align:center; ">XOB</td><td style="font-family:Verdana; text-align:center; ">176.8239859</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">1220342.397</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">177.854252</td><td >*</td><td style="text-align:center; ">-61.14367851</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">1220386.33</td><td style="text-align:center; ">XSAG</td><td style="text-align:center; ">XSAG</td><td style="font-family:Verdana; text-align:center; ">59.01305492</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">1220403.54</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">238.419954</td><td >*</td><td style="text-align:center; ">-60.56570204</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">1220407.011</td><td style="text-align:center; ">XER</td><td style="text-align:center; ">XER</td><td style="font-family:Verdana; text-align:center; ">19.59009842</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; ">1220415.186</td><td style="text-align:center; ">XRD</td><td style="text-align:center; ">XRD</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; ">1220426.601</td><td style="text-align:center; ">XER</td><td style="text-align:center; ">XER</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:center; ">1220445.343</td><td style="text-align:center; ">XSAG</td><td style="text-align:center; ">XSAG</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:center; ">1220459.774</td><td style="text-align:center; ">XCDW</td><td style="text-align:center; ">XCDW</td><td style="font-family:Verdana; text-align:center; ">0.95721941</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:center; ">1220460.731</td><td style="text-align:center; ">XCDW</td><td style="text-align:center; ">XCDW</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:center; ">1220464.106</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">287.1153245</td><td >*</td><td style="text-align:center; ">-48.69537047</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:center; ">1220506.526</td><td style="text-align:center; ">XOB</td><td style="text-align:center; ">XOB</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:center; ">1220512.802</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; text-align:center; ">347.5717253</td><td >*</td><td style="text-align:center; ">-60.45640083</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:center; ">1220573.258</td><td style="text-align:center; ">XR</td><td style="text-align:center; ">XR</td><td style="font-family:Verdana; ">#NUM!</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "></table>

I might be going about this all wrong and maybe need to do some vba. What do you all think?

T. Valko · Jun 14, 2011

bboysen said:
So this is what i am getting!

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 88px"><COL style="WIDTH: 88px"><COL style="WIDTH: 175px"><COL style="WIDTH: 173px"><COL style="WIDTH: 88px"><COL style="WIDTH: 88px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>*</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Station</TD><TD style="TEXT-ALIGN: center">Code</TD><TD style="TEXT-ALIGN: center">Code</TD><TD style="TEXT-ALIGN: center">Difference with Formaula</TD><TD>*</TD><TD style="TEXT-ALIGN: center">Real Difference</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1220225.686</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">60.96368042</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-60.96368042</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1220286.65</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">116.7105735</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-55.74689307</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1220329.702</TD><TD style="TEXT-ALIGN: center">XOB</TD><TD style="TEXT-ALIGN: center">XOB</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">176.8239859</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1220342.397</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">177.854252</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-61.14367851</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">1220386.33</TD><TD style="TEXT-ALIGN: center">XSAG</TD><TD style="TEXT-ALIGN: center">XSAG</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">59.01305492</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">1220403.54</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">238.419954</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-60.56570204</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">1220407.011</TD><TD style="TEXT-ALIGN: center">XER</TD><TD style="TEXT-ALIGN: center">XER</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">19.59009842</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">1220415.186</TD><TD style="TEXT-ALIGN: center">XRD</TD><TD style="TEXT-ALIGN: center">XRD</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">1220426.601</TD><TD style="TEXT-ALIGN: center">XER</TD><TD style="TEXT-ALIGN: center">XER</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">1220445.343</TD><TD style="TEXT-ALIGN: center">XSAG</TD><TD style="TEXT-ALIGN: center">XSAG</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">1220459.774</TD><TD style="TEXT-ALIGN: center">XCDW</TD><TD style="TEXT-ALIGN: center">XCDW</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">0.95721941</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">1220460.731</TD><TD style="TEXT-ALIGN: center">XCDW</TD><TD style="TEXT-ALIGN: center">XCDW</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">1220464.106</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">287.1153245</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-48.69537047</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">1220506.526</TD><TD style="TEXT-ALIGN: center">XOB</TD><TD style="TEXT-ALIGN: center">XOB</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">1220512.802</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">347.5717253</TD><TD>*</TD><TD style="TEXT-ALIGN: center">-60.45640083</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">1220573.258</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="TEXT-ALIGN: center">XR</TD><TD style="FONT-FAMILY: Verdana">#NUM!</TD><TD>*</TD><TD>*</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY></TBODY></TABLE>

I might be going about this all wrong and maybe need to do some vba. What do you all think?

The formula I suggested is for finding the difference between the 1st and 2nd instance of some code. Like: 2nd instance minus 1st instance.

It looks like you want the opposite: 1st instance minus 2nd instance. And then, you want nth instance minus nth instance +1.

Can you sort the data so that all the code types will be grouped together?

bboysen · Jun 14, 2011

Biff thanks for the quick reply.

I can sort it to any order but the problem with that the station is a formula that is derived from another cell.

Here is what I have in the bigger picture:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

MP

2d Station

3d Station

Point No

Northing

Easting

Elevation

Code

2d Distance

3d Distance

Delta Elevation

Weld No.

Pipe / Serial No.

Heat No.

Cover

2

216.65

12198+00.00

550001

13944005.318

1047205.803

5077.579

XLE

*

5GC1750

T45936B

*

3

216.66

12198+60.52

550002

13943948.181

1047185.867

5077.163

XR

60.52

-0.42

AML1639

5GC1751

T45895B

3.9

4

216.68

12199+21.49

12199+21.50

550003

13943890.959

1047164.800

5077.804

XR

60.98

0.64

AML1640

5GC1752

T45895

3.9

5

216.69

12199+82.44

550004

13943833.590

1047144.230

5077.810

XR

60.95

0.01

AML1641

5GC1753

T45936B

3.8

6

216.70

12200+43.09

12200+43.11

550005

13943776.573

1047123.558

5079.151

XR

60.65

60.66

1.34

AML1642

5GC1754

T45895

3.8

7

216.71

12201+03.97

12201+04.00

550006

13943719.373

1047102.687

5080.044

XR

60.89

60.90

0.89

AML1643

5GC1755

T45895

3.9

8

216.72

12201+65.02

12201+65.05

550007

13943662.025

1047081.773

5080.563

XR

61.04

0.52

AML1644

5GC1756

T45936B

3.8

9

216.73

12202+25.66

12202+25.69

550008

13943604.958

1047061.264

5080.431

XR

60.64

-0.13

AML1645

5GC1757

T45936A

4.2

10

216.74

12202+86.61

12202+86.65

550009

13943547.509

1047040.891

5079.371

XR

60.95

60.96

-1.06

AML1646

5GC1758

T45895

4.2

11

216.75

12203+29.66

12203+29.70

500001

13943507.098

1047026.046

5079.095

XOB

43.05

-0.28

*

4.2

12

216.76

12203+42.36

12203+42.40

500002

13943495.048

1047022.053

5079.014

XR

12.69

-0.08

CTI146

5GC1760

T45895

4.3

13

216.76

12203+86.29

12203+86.33

500003

13943453.841

1047006.829

5078.442

XSAG

43.93

-0.57

*

5.2

14

216.77

12204+03.50

12204+03.54

500004

13943437.623

1047001.076

5078.733

XR

17.21

0.29

AML1647

5GC1762

T45936B

5.1

15

216.77

12204+06.96

12204+07.01

500005

13943434.382

1046999.840

5078.850

XER

3.47

0.12

*

5.1

16

216.77

12204+15.14

12204+15.19

500006

13943426.660

1046997.160

5079.006

XRD

8.17

8.18

0.16

*

17

216.77

12204+26.55

12204+26.60

500007

13943415.933

1046993.259

5079.109

XER

11.41

0.10

*

5

18

216.77

12204+45.29

12204+45.34

500008

13943398.263

1046987.027

5079.538

XSAG

18.74

0.43

*

5

19

216.78

12204+59.71

12204+59.77

500014

13943384.734

1046982.041

5080.141

XCDW

14.42

14.43

0.60

*

20

216.78

12204+60.66

12204+60.73

500015

13943383.841

1046981.697

5080.163

XCDW

0.96

0.02

*

21

216.78

12204+64.03

12204+64.11

500009

13943380.554

1046980.957

5080.358

XR

3.37

0.20

CTI147

5GC1759

T45936B

5.1

Spread 5 North End Master

As you can see i have a ton for formulas in this spread sheet and this is only the beginning. The Code "XR" will some times have 1-10 row between the two different "XR" shots ones.

I do not know if this makes any sense to you but it is kind of hard to explain. Thanks for all of your

.

T. Valko · Jun 14, 2011

bboysen said:
Biff thanks for the quick reply.

I can sort it to any order but the problem with that the station is a formula that is derived from another cell.

Here is what I have in the bigger picture:

Excel Workbook
A B C D E F G H I J K L M N O
1 MP 2d Station 3d Station Point No Northing Easting Elevation Code 2d Distance 3d Distance Delta Elevation Weld No. Pipe / Serial No. Heat No. Cover
2 216.65 12198+00.00 12198+00.00 550001 13944005.318 1047205.803 5077.579 XLE * * * * 5GC1750 T45936B *
3 216.66 12198+60.52 12198+60.52 550002 13943948.181 1047185.867 5077.163 XR 60.52 60.52 -0.42 AML1639 5GC1751 T45895B 3.9
4 216.68 12199+21.49 12199+21.50 550003 13943890.959 1047164.800 5077.804 XR 60.98 60.98 0.64 AML1640 5GC1752 T45895 3.9
5 216.69 12199+82.44 12199+82.44 550004 13943833.590 1047144.230 5077.810 XR 60.95 60.95 0.01 AML1641 5GC1753 T45936B 3.8
6 216.70 12200+43.09 12200+43.11 550005 13943776.573 1047123.558 5079.151 XR 60.65 60.66 1.34 AML1642 5GC1754 T45895 3.8
7 216.71 12201+03.97 12201+04.00 550006 13943719.373 1047102.687 5080.044 XR 60.89 60.90 0.89 AML1643 5GC1755 T45895 3.9
8 216.72 12201+65.02 12201+65.05 550007 13943662.025 1047081.773 5080.563 XR 61.04 61.04 0.52 AML1644 5GC1756 T45936B 3.8
9 216.73 12202+25.66 12202+25.69 550008 13943604.958 1047061.264 5080.431 XR 60.64 60.64 -0.13 AML1645 5GC1757 T45936A 4.2
10 216.74 12202+86.61 12202+86.65 550009 13943547.509 1047040.891 5079.371 XR 60.95 60.96 -1.06 AML1646 5GC1758 T45895 4.2
11 216.75 12203+29.66 12203+29.70 500001 13943507.098 1047026.046 5079.095 XOB 43.05 43.05 -0.28 * * * 4.2
12 216.76 12203+42.36 12203+42.40 500002 13943495.048 1047022.053 5079.014 XR 12.69 12.69 -0.08 CTI146 5GC1760 T45895 4.3
13 216.76 12203+86.29 12203+86.33 500003 13943453.841 1047006.829 5078.442 XSAG 43.93 43.93 -0.57 * * * 5.2
14 216.77 12204+03.50 12204+03.54 500004 13943437.623 1047001.076 5078.733 XR 17.21 17.21 0.29 AML1647 5GC1762 T45936B 5.1
15 216.77 12204+06.96 12204+07.01 500005 13943434.382 1046999.840 5078.850 XER 3.47 3.47 0.12 * * * 5.1
16 216.77 12204+15.14 12204+15.19 500006 13943426.660 1046997.160 5079.006 XRD 8.17 8.18 0.16 * * * *
17 216.77 12204+26.55 12204+26.60 500007 13943415.933 1046993.259 5079.109 XER 11.41 11.41 0.10 * * * 5
18 216.77 12204+45.29 12204+45.34 500008 13943398.263 1046987.027 5079.538 XSAG 18.74 18.74 0.43 * * * 5
19 216.78 12204+59.71 12204+59.77 500014 13943384.734 1046982.041 5080.141 XCDW 14.42 14.43 0.60 * * * *
20 216.78 12204+60.66 12204+60.73 500015 13943383.841 1046981.697 5080.163 XCDW 0.96 0.96 0.02 * * * *
21 216.78 12204+64.03 12204+64.11 500009 13943380.554 1046980.957 5080.358 XR 3.37 3.37 0.20 CTI147 5GC1759 T45936B 5.1
Spread 5 North End Master

As you can see i have a ton for formulas in this spread sheet and this is only the beginning. The Code "XR" will some times have 1-10 row between the two different "XR" shots ones.

I do not know if this makes any sense to you but it is kind of hard to explain. Thanks for all of your .

Based on the data in post #5 it looks like this station value is in ascending order.

Is that always the case?

bboysen · Jun 14, 2011

Yes the station is always in ascending order and is calc off of the 2d distance and the 3d distance plus the Cell above it.

Thanks of your help!

T. Valko · Jun 14, 2011

bboysen said:
Yes the station is always in ascending order and is calc off of the 2d distance and the 3d distance plus the Cell above it.

Thanks of your help!

OK I think I have it.

Book1

A

B

C

2

1220225.686

XR

-60.964

3

1220286.650

XR

-55.747

4

1220329.702

XOB

5

1220342.397

XR

-61.143

6

1220386.33

XSAG

7

1220403.540

XR

-60.566

8

1220407.011

XER

9

1220415.186

XRD

10

1220426.601

XER

11

1220445.343

XSAG

12

1220459.774

XCDW

13

1220460.731

XCDW

14

1220464.106

XR

-48.696

15

1220506.526

XOB

16

1220512.802

XR

-60.456

17

1220573.258

XR

Sheet1

This array formula** entered in C2 and copied down:

=IF(B2="xr",IF(A2=INDEX(A$2:A$17,MATCH(1E100,IF(B$2:B$17="xr",A$2:A$17))),"",A2-INDEX(A3:A$17,MATCH("xr",B3:B$17,0))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If statement

bboysen

New Member

Excel Facts

Steve=True

Well-known Member

bboysen

New Member

T. Valko

Well-known Member

bboysen

New Member

T. Valko

Well-known Member

bboysen

New Member

T. Valko

Well-known Member

bboysen

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

If statement

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

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