Find and replace using two lists (not in two lists)

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
I posted this last week but did not recieve a reply. Since then i have not found a solution.
I have a list with two columns and 1000 rows, i want to use this to replace items found in another list as follows.
If the string in A1 is found replace it with B1. And do this for about 1000 items in another sheet of nearly 20,000 rows. Seems simple enough? Maybe this can already be done with find replace tools found in excel, i cant find them though....
any help would be great :D
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Not sure I quite understand, but this is what I have done

SHeet 1

A1 = a; A2 = b; A3 = c
B1 = aa; B2 = bb; B3 = cc

Sheet 2

A1 = a; A2 = d; A3 = e

Back on SHeet 1 I have entered this formula in C1

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),A1,B1)

and copied it down.

How does this suit?
 

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
I need something that will use the list (of about 1000 rows) as a type of translator. So when it find that string in A1 it will replace that with B1. I would like this to happen autoamtically because it has to search for the strings in A1:A1000 in a sheet of about 20,000 rows.
The reason i want to do this is to save time finding and replacing by typing each of the 1000 strings into the excel find/replace dialouge.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Kyle775

I wrote a small code that does what you want (if I understood well).

As an example I have in worksheet Sheet1 the colours in English in column A and in French in column B. In worksheet Sheet2 I have sentences that include the colours.
I ran the code and post the result. I copied the original sentences to column D so that you could compare.

Hope this is helps you get started
PGC

Code:
Option Explicit

Sub ReplaceSynonyms()
Dim rR1 As Range, rR2 As Range, rC As Range

With Worksheets("Sheet1")
    Set rR1 = .Range("A1", "A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

With Worksheets("Sheet2")
    Set rR2 = .Range("A1", "A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

For Each rC In rR1
    rR2.Replace What:=rC, Replacement:=rC.Offset(0, 1), _
    MatchCase:=False
Next
End Sub
Book1
ABCD
1blacknoir
2bluebleu
3brownbrun
4graygris
5greenvert
6orangeorange
7pinkrose
8purplepourpre
9redrouge
10skybluebleuciel
11violetviolet
12whiteblanc
13yellowjaune
14
Sheet1
Book1
ABCDEF
1theskyisbleu*theskyisblue*
2theeggisjaune*theeggisyellow*
3thebirdisvertandrouge*thebirdisgreenandred*
4thenightisnoir*thenightisblack*
5thesnowisblanc*thesnowiswhite*
6thehairisgrey*thehairisgrey*
7***
8
Sheet2
 

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230

ADVERTISEMENT

This looks like exactly what i need! thanks!
will it handle blank cells? for example if i wanted to change something to a blank.
Also im not familiar with VB code, how do i adjust the range or size of the "translator" sheet?
thanks again.

edit: also i dont want to apply the translation to the entire sheet only one column. is this possible...
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

will it handle blank cells? for example if i wanted to change something to a blank.

Yes. If in my example the name corresponding to Blue, in cell B2 of Sheet1, is blank, then the sentence in Sheet2!A1 "the sky is blue" becomes "the sky is ".

how do i adjust the range or size of the "translator" sheet?

In the code I posted it is automatic.

In Sheet1:

The words to be replaced are in column A
The corresponding new words are 1 cell to the right in column B.

The code sets the "words to be replaced" range from A1 to the last cell in column A with data.

In Sheet2:

The sentences where the replacements will take place are in column A.

The code sets the range where the sentences are from A1 to the last cell in column A with data.

Although defining the ranges automatically is more convenient you may test the code with hardcoded ranges by changing the SET statements.

For instance:

Code:
With Worksheets("Sheet1") 
    Set rR1 = .Range("A1:A100")
End With 

With Worksheets("Sheet2") 
    Set rR2 = .Range("A1:A1000")
End With

To test do the following

Right-click the worksheet tab, choose view code.
In the menu of the vba editor Insert>Module.
Paste this code in the module.
Go back to the worksheet and in Tools>Macro>Macros, choose ReplaceSynonyms and click run.

Hope this helps
PGC
 

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
This is great! ive saved this macro, so many times i have solved this problem (or had someone solve it for me) and i forget how or lose the code... i didnt think it would be so useful until i need it again :)
thanks again this saved me at least 6 hours (today)
 

Forum statistics

Threads
1,137,063
Messages
5,679,401
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top