Search inverse

[spiz]

Hi,
I have data that was found via vlookup and need to find the matching data with the last letter as it's inverse.
Meaning "R" for "F", or "F" for "R".
each inquiry will not have the same amount of letters/numbers before the "R" or "F", but will have a format like this:

data - R
or
data - F

i tried converting the "R" to an "F" and vice versa and then trying another vlookup, but it's not working.

Any help is greatly appreciated.

-spiz-

 

[Richard Schollar]

Hi & Welcome to the Board!

I have to be honest I have not understood at all what you are trying to do - how come R is the inverse of F?

 

[spiz]

well, perhaps inverse is the wrong word. perhaps...alternate?

- i'm trying to find the data returned from the original vlookup

- in another vlookup that has everything the same

- except for the last letter

- which will be the opposite of the last letter returned from original lookup, which is a F or a R.

the last letter is also separated from the first part of the data by a space, hyphen and another space.
Hope that makes more sense.

 

[Richard Schollar]

Nope, I'm afraid not - could you post some actual sample data (just a few records), show the current formula you are using then state from this data what the inverse (or alternate) would then be?

 

[spiz]

ok...
the object of this project is to show the cable path of 3 cables that connect 2 computer systems. flow is:

[server (cable 1)] - [patch (cable 2)] - [server (cable 3)] = 6 connections to display (from and to).

i have concatenated the input data, which is input from 3 cells
so this: (this is done in 2 column sets...one for cables going to, the other for cables coming from so it can be searched from either cable within the system)
rack elevation jack
11 a01 ja - r
2 b1 t - r
or
11 a01 ja - f
2 b1 t - f
becomes this:
11a01ja - r
2b1t - r
12a02jq - f
2b1t - f
after this, this is where i'm stuck.

i have about 1200 entries of this data to work from in 2 separate columns.
(one side is the cable going to path, the other side is the cable coming from path.)

So far i can only figure out how to show the first cables connections.
the first cable is always ending in "F" signifying that it's connected in the front.
the second cable will have the same rack, elevation and jack information, but ending with a "R", signifying that it's connected in the rear. I'm also not sure which column it will be located in, but that's not a big problem. Trying to locate the data is, b/c what the new search is for is not the same as what was returned.

i hope i didn't confuse you too much.

 

[Sigh]

My interpretation is that the current data has transposed the R or F from the original data, is this correct?

 

[spiz]

well...i wrote a function to change the R to an F or vice versa, but it's still not locating the data i need.

what do you think...take out the " - R/F" completely from a new concatenated list of copied data,
search/match the data that was brought back from the first inquiry

then, match data found to original list with the R's and F's to find the middle cable connections.
i'm trying this now...
let me know if you what you think