Need to replace one dynamic variable with another dynamic variable

[djreason]

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.

 

[Pecoflyer]

HOW TO GET FURTHER HELP WITH A WORKBOO
For further help with it why not join our forums (shown i
the link below) it's completely free, if you do join you will have th
opportunity to add attachments to your posts so you can add workbooks t
better illustrate your problems and get help directly with them. Also i
you do join please post in this thread (link found below) so that peopl
who have been following or helping with this query can continue to d
so. :

--
Pecoflye

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html
really IS fast

 

[Jacob Skaria]

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes

 

[djreason]

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?

 

[djreason]

I think I need a statement that completes the following:

If Sheet1!C2 contains Sheet2!A1:A50000, replace Sheet2!A1:A50000 on
Sheet1!C2:C50000 with Sheet2!B1:B50000

hope this helps someone cause I dont know how to write this argument
correctly so it repopulates my full descriptions with the expanded years.

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes

 

[Jacob Skaria]

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes

 

[djreason]

I find that the expression you provided works if the last part of my
descriptions end in the short year ranges. However, in ALL of my
descriptions, the short year ranges can be found anywhere within a
description that is in excess of 1000 words.

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


:

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.

 

[djreason]

Okay...

I got your suggestion to work when i created a brand new workbook with sheet
1 and sheet 2 as described in our short example.

However, now when I take that formula to my actual worksheet and make the
necessary changes to reference the correct cells and sheets, I get #N/A
again.

OUt of curiosity, what is the 15 in your function? Would that be important
if each description is different in length?

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


:

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.

 

[djreason]

Additionally,

All of my descriptions also contain other numbers. I dont know if that
interferes with this function locating the numbers I want to manipulate.

hope this helps and thanks for the help!

Okay...

I got your suggestion to work when i created a brand new workbook with sheet
1 and sheet 2 as described in our short example.

However, now when I take that formula to my actual worksheet and make the
necessary changes to reference the correct cells and sheets, I get #N/A
again.

OUt of curiosity, what is the 15 in your function? Would that be important
if each description is different in length?

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?



:

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


:

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.

 

[Jacob Skaria]

Other numerics is the cause of the issue. The earlier formula picks the text
string from the 1st numeric to 15 characters. Try the below formula which
will look for a "-" in the query string and pick the 4 numerics in front of
"-" to the end.


=VLOOKUP(TRIM(MID(A2,FIND("-",A2)-4,10)),Sheet2!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria

Additionally,

All of my descriptions also contain other numbers. I dont know if that
interferes with this function locating the numbers I want to manipulate.

hope this helps and thanks for the help!

Okay...

I got your suggestion to work when i created a brand new workbook with sheet
1 and sheet 2 as described in our short example.

However, now when I take that formula to my actual worksheet and make the
necessary changes to reference the correct cells and sheets, I get #N/A
again.

OUt of curiosity, what is the 15 in your function? Would that be important
if each description is different in length?

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria


:

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?



:

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


:

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.