lookup loop to find values from a list inside a string of values

Hi
A couple of Qs first.
1. Are the values from Column A that you're wanting to check against List 1 always the characters between the 1st and 2nd spaces? i.e. from

2 XP6-45-R UP 65.50 45 200 10 2 69.1 New 221w Tx2A/Rx2A 2Rd+1Pu Fiber New 1

will it always be

XP6-45-R

2.Are the values from Column A that you're wanting to check against List 2 always 3 digits and always before the "w Tx" string of characters?

Rgds

Hi Richard,
1. yes it does look like that, I have just gone through a whole lot of data and the value, be it F-BXD-63406380_19 or LBX-4517DS-A20M etc does look like it is sitting between the first two spaces (the number of values in the string between the two spaces vary though).

2. Not exactly, here is a sample of some data, I think you could say that the number preceeds a w (but how do you differentiate between 821w and New?)
here is a sample line by line (I insert (line x) for clarity)

HTML Code: 

Is this any help?

Thank you
Bea

Hi,

Thanks for that.

I think we should be able to differentiate between nnnw and New, perhaps by looking for the string "w Tx". Does this always appear after the 821w type references?

I'll take a look tomorrow - unless someone steps in in the meantime.

Rgds

Hi,

In List1 you have a value -

LBX-3319DS-TOM

,
yet in column A the value to which this relates in in A6 is

4 LBX-3319DS-T0M UP 61.90 34 80 15 1 101.5 New 811w Tx1A/Rx1A 1Rd+1Pk Fiber New 1

The first contain a "O" (letter O in TOM), whilst the second contains a "0" (zero value in T0M). Is this a typo and if so which is correct.

Regards

Hi,

Assuming you align the zeros and letter "O" in the character strings "TOM" or "T0M" - see my last post, the following should work. The only other assumption is that the first space character in the shorter rows is after position 4 and that the numeric value e.g. 222 is always 3 digits. If not please post back since this will need modifying.

List 1 formula

=IF(ISERROR(MATCH(MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",2))-FIND(" ",A2)-1),O:O,FALSE)),"",INDEX(O:O,MATCH(MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",2))-FIND(" ",A2)-1),O:O,FALSE),1))

List 2 formula

=IF(FIND(" ",A2)>4,VALUE(LEFT(A2,3)),INDEX(Q:Q,MATCH(VALUE(MID(A2,FIND("*",SUBSTITUTE(A2,"New ","*"))+4,3)),Q:Q,FALSE),1))

HTH

Hi Richard,
I am not sure why we are getting a O and 0,it should be what ever was imbeded inside the string.
I had a lot of trouble getting onto this web site today so couldn't respond earlier, thank you for the formulas I will try them today.

Here is some more sample data which I have put their line numbers to for clarity.

Thanks so much


Bea

PHP Code: 

line 1:1 XP6-45-R UP 65.50 45 80 10 2 69.1 New 211w Tx1A/Rx1A 1Rd+1Pu Fiber New 1
line 2:212w Rx1B 1Bl+1Pu Fiber New
Line 3:2 XP6-45-R UP 65.50 45 200 10 2 69.1 New 221w Tx2A/Rx2A 2Rd+1Pu Fiber New 1
line 4:222w Rx2B 2Bl+1Pu Fiber New
line 5:3 XP6-45-R UP 65.50 45 320 10 2 69.1 New 231w Tx3A/Rx3A 3Rd+1Pu Fiber New 1
line 6:232w Rx3B 3Bl+1Pu Fiber New
line 7:4 LBX-3319DS-T0M UP 61.90 34 80 15 1 101.5 New 811w Tx1A/Rx1A 1Rd+1Pk Fiber New 1
line 8:812w Rx1B 1Bl+1Pk Fiber New
line 9:5 LBX-3319DS-T0M UP 61.90 34 200 15 7 101.5 New 821w Tx2A/Rx2A 2Rd+1Pk Fiber New 1
line 10:822w Rx2B 2Bl+1Pk Fiber New

PCS1900 


from your data as given (assuming numbers will always be 3 digits long
=IF(LEN(A1)>50,MID(A1,2,FIND(" UP",A1)-1-FIND(" ",A1)+1),"")
and
=IF(LEN(A1)>50,MID(A1,FIND("w Tx",A1)-3,3),LEFT(A1,3))