Substituting texts from specified list

[AdarsH]

Hello.

Am new to excel and i need some help for an excel formula. Below is the scenario:

Got the following data in different cells:

And on a different sheet in the same workbook, have 45 different values to be converted:

What is required?

Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.

Basically, for the 1st Cell for eg..

2232-2232-

I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.

Result shall look like below:

20-20-

Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:

2210-22G1-45R1-45G1-20R1-20GP-

It becomes difficult for me. What i need exactly is:

Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.

Your help will be much appreciated.

 

[sanrv1f]

Welcome to the board,

It would be almost impossible to do it with formula, you can try this


goto VBE (Alt + F11)
Insert -> Module
Place the code
Quit VBE (Alt + Q)
Run the macro (Alt + F8)

Sub replce()
Dim cl As Range
For Each cl In Worksheets("Sheet2").Range("A1:A45")
    Worksheets ("Sheet1")
    Range("BE1:BE100").Replace cl, cl.Offset(, 1)
Next
End Sub

Sheet1 is the data sheet, sheet2 is where the find and replace list is stored

also note that, In future, It would be much better to post sample data tables instead of image, since it would be hard for the helpers to reproduce the problem in their sheets.

 

[texasalynn]

would be easier to help you if your examples weren't pictures, so the data can be copied. Try using Jenny or the beta from Richard Schollar

 

[AdarsH]

Am new to the board. Thanks for your initial help. I'll try to use the add-ins to provide with sample data.

 

[AdarsH]

Here are the data sets for sheet 1:

*This is only part of the file. Have 100s of lines like these with multiple values as defined in tables for sheet 2

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:180px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >BE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >CONTAINER GROUP</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >20GP-40HC-20GP-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2232-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4510-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >4510-4510-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4510-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >2210-2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >2210-2210-2210-2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">2210-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">2210-</td></tr><tr style="height:34px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:51px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >2210-2210-2210-2210-2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">2210-</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:102px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr><tr style="height:546px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-2210-</td></tr></table><br />

and below are the values in the tables:

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">2065-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2100-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2210-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2215-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2232-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2251-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2261-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2270-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2332-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">2430-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">4200-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4310-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4351-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4361-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">4430-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">4510-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">4532-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >20DV-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >20GP-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >20GP-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >20R1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >20RE-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >22G0-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >22G1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >22G2-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >22P3-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >22R1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >22U1-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >22V0-</td><td style="text-align:right; ">20-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >40DR-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >40GP-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >40HC-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >40HQ-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >40RE-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >40RQ-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td >40U1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td >42G1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >42P3-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td >42U0-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td >45G0-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td >45G1-</td><td style="text-align:right; ">40-</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td >45R1-</td><td style="text-align:right; ">40-</td></tr></table> <br /><br />

 

[AdarsH]

any clue how to do it? tried the VBA sanrv1f gave, but not working :S

 

[sanrv1f]

thats an error on my part, apologies,

try this

Sub replce()
Dim cl As Range
For Each cl In Worksheets("Sheet2").Range("A1:A45")
    Worksheets("Sheet1").Range("BE1:BE100").Replace What:=cl, Replacement:=cl.Offset(, 1), LookAt:=xlPart
Next
End Sub

 

[AdarsH]

Hi Sankar. I tired the secod VBA code, still nothing. The code runs, but does not replace anything.

 

[sanrv1f]

did you change the Sheet names and the range to suit the actual data?

 

[AdarsH]

Tried changing range "BE1:BE100" to "BE:BE" for the whole column and "A1:A45" to "A1:A42", but no vain. Please check you email, i sent you the whole file.