If statement

[bboysen]

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?

 

[bboysen]

Thanks so much you have saved the day. I would love to know how you came up with what you did and how each statement works, if you have time to explain.

Thanks again!

Brad

 

[T. Valko]

Thanks so much you have saved the day. I would love to know how you came up with what you did and how each statement works, if you have time to explain.

Thanks again!

Brad

I'll have to get back to you tomorrow. I'm getting ready to call it a day!

 

[T. Valko]

Thanks so much you have saved the day. I would love to know how you came up with what you did and how each statement works, if you have time to explain.

Thanks again!

Brad

=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))),"")

Ok, here's how it works...

First we test the cell in column B to see if it's the correct code:

=IF(B2="xr",

If it isn't, then all the stuff in the middle of the formula is bypassed and the formula returns a blank:

=IF(B2="xr",....."")

If the code is the code we're looking for then we test the station value to see if it's the last station value for the code of interest:

IF(A2=INDEX(A$2:A$17,MATCH(1E+100,IF(B$2:B$17="xr",A$2:A$17)))

If the station value is the last station value then we need to return a blank (because there are no other station values to process):

IF(A2=INDEX(A$2:A$17,MATCH(1E+100,IF(B$2:B$17="xr",A$2:A$17))),""

If it's not the last station value then we need to find the next station value for the code of interest:

INDEX(A3:A$17,MATCH("xr",B3:B$17,0))

Then subtract the two station values:

A2-INDEX(A3:A$17,MATCH("xr",B3:B$17,0))

 

[bboysen]

Hello once again!!!!

I have this formula that works but I need to expand it!

This is what i have:
=IF(K4="XR",IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(K$5:K14="XR,B$5:B14))),"",B4-INDEX(B5:B14,MATCH("XR",K5:K14,0))),"")

This will give me a distance from once xr to the next xr but now i need to get it to work from xr to either a xr, xw, xwtc.

I have tried or and using the +, but that will not work i just get #VALUE.

So in the B column i have a number lets say

B2= 100.00 & K2=XR
B3= 150.00 & K3=XW distance between the two is 50 in say column AD
B4= 160.00 & K4=XWTC distance between the two is 10 in say column AD

and so on.

It is the same file as before just with more codes.

If you need more info please let me know. I have hit a dead end and hope the master minds on here can help.

Thanks

 

[T. Valko]

Hello once again!!!!

I have this formula that works but I need to expand it!

This is what i have:
=IF(K4="XR",IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(K$5:K14="XR",B$5:B14))),"",B4-INDEX(B5:B14,MATCH("XR",K5:K14,0))),"")

This will give me a distance from once xr to the next xr but now i need to get it to work from xr to either a xr, xw, xwtc.

I have tried or and using the +, but that will not work i just get #VALUE.

So in the B column i have a number lets say

B2= 100.00 & K2=XR
B3= 150.00 & K3=XW distance between the two is 50 in say column AD
B4= 160.00 & K4=XWTC distance between the two is 10 in say column AD

and so on.

It is the same file as before just with more codes.

If you need more info please let me know. I have hit a dead end and hope the master minds on here can help.

Thanks

Maybe this...

Replace this:

B4-INDEX(B5:B14,MATCH("XR",K5:K14,0))

With this:

B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"xr","xw","xwtc"},0)),0))

 

[bboysen]

Maybe this...

Replace this:

B4-INDEX(B5:B14,MATCH("XR",K5:K14,0))

With this:

B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"xr","xw","xwtc"},0)),0))

So I gave that a shot and make it like this

=IF(K4={"XR","XW","XWTC"},IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(K$5:K14={"XR","XW","XWTC"},B$5:B14))),"",B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"xr","xw","xwtc"},0)),0))),"")

but i do not know if that is what you wanted me to do! Now i get a #N/A!

 

[T. Valko]

So I gave that a shot and make it like this

=IF(K4={"XR","XW","XWTC"},IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(K$5:K14={"XR","XW","XWTC"},B$5:B14))),"",B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"xr","xw","xwtc"},0)),0))),"")

but i do not know if that is what you wanted me to do! Now i get a #N/A!

Replace this:

=IF(K4={"XR","XW","XWTC"},

With this:

=IF(OR(K4={"XR","XW","XWTC"}),

 

[bboysen]

Replace this:

=IF(K4={"XR","XW","XWTC"},

With this:

=IF(OR(K4={"XR","XW","XWTC"}),

Thanks so much you shed the light on what I needed to do!

This is what I ended up with

{=IF(OR(K4={"XR","XW","XWTC"}),IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(OR(K$5:K14={"XR","XW","XWTC"}),B$5:B14))),"",B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"XR","XW","XWTC"},0)),0))),"")}

And works just how I need it to. Thanks a bunch man you have saved the day again.

 

[T. Valko]

Thanks so much you shed the light on what I needed to do!

This is what I ended up with

{=IF(OR(K4={"XR","XW","XWTC"}),IF(B4=INDEX(B$5:B14,MATCH(1E+100,IF(OR(K$5:K14={"XR","XW","XWTC"}),B$5:B14))),"",B4-INDEX(B5:B14,MATCH(TRUE,ISNUMBER(MATCH(K5:K14,{"XR","XW","XWTC"},0)),0))),"")}

And works just how I need it to. Thanks a bunch man you have saved the day again.

Good deal. Thanks for the feedback!