Find and Replace against set of rules in 2nd column

H

Hank Rouse

Looking for a tool, or code that does the following.

Replace Special Character in Sheet 1, Column A. [using "^" as my special
character, remove the quotes. Could change if using something else would be
better.]

Replace ^ with contents of Sheet 1, Column B.

For example I have:

Column A Column B

12^798 xyz
0^5131 abc
5296^4 efg

Column A represents item #'s out of our catalogue.
Column B represents Supplier

So in the end I would get:

Column A Column B

12xyz798 xyz
12abc798 abc
12efg798 efg
0zyx5131 xyz
0abc5131 abc
0efg5131 efg
5296xyz4 xyz
5296abc4 abc
5296efg4 efg

Presently there are 59 criteria in Column B and I have to perform this
against 19 sites. So doing this manual via Find and Replace takes forever.

VBA code, or something similar would be great.

Thanks in Advance
 
R

RagDyeR

You could make Column C into a "helper" column and enter this in C1:

=SUBSTITUTE(A1,"^",B1)

And copy down as needed.

You could then delete the Text formula in Column C, and leave the data
behind by selecting Column C, right click and choose "Copy".
Right click again, and choose "PasteSpecial",
And click on "Values", then <OK>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Looking for a tool, or code that does the following.

Replace Special Character in Sheet 1, Column A. [using "^" as my special
character, remove the quotes. Could change if using something else would be
better.]

Replace ^ with contents of Sheet 1, Column B.

For example I have:

Column A Column B

12^798 xyz
0^5131 abc
5296^4 efg

Column A represents item #'s out of our catalogue.
Column B represents Supplier

So in the end I would get:

Column A Column B

12xyz798 xyz
12abc798 abc
12efg798 efg
0zyx5131 xyz
0abc5131 abc
0efg5131 efg
5296xyz4 xyz
5296abc4 abc
5296efg4 efg

Presently there are 59 criteria in Column B and I have to perform this
against 19 sites. So doing this manual via Find and Replace takes forever.

VBA code, or something similar would be great.

Thanks in Advance
 
H

Hank Rouse

I found this, however I need some help with it.

Again, I want to replace a special character "^" with the contents of Column
B.

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(1)
End With

With Worksheets("Sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells
RngToChange.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub



Hank Rouse said:
Sorry, you missed the whole point of the loop through.



RagDyeR said:
You could make Column C into a "helper" column and enter this in C1:

=SUBSTITUTE(A1,"^",B1)

And copy down as needed.

You could then delete the Text formula in Column C, and leave the data
behind by selecting Column C, right click and choose "Copy".
Right click again, and choose "PasteSpecial",
And click on "Values", then <OK>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Looking for a tool, or code that does the following.

Replace Special Character in Sheet 1, Column A. [using "^" as my special
character, remove the quotes. Could change if using something else
Click to expand...
would
be
better.]

Replace ^ with contents of Sheet 1, Column B.

For example I have:

Column A Column B

12^798 xyz
0^5131 abc
5296^4 efg

Column A represents item #'s out of our catalogue.
Column B represents Supplier

So in the end I would get:

Column A Column B

12xyz798 xyz
12abc798 abc
12efg798 efg
0zyx5131 xyz
0abc5131 abc
0efg5131 efg
5296xyz4 xyz
5296abc4 abc
5296efg4 efg

Presently there are 59 criteria in Column B and I have to perform this
against 19 sites. So doing this manual via Find and Replace takes forever.

VBA code, or something similar would be great.

Thanks in Advance
Click to expand...
Click to expand...
 
K

kcc

I'm never a fan of using code when a formula will do.
Assuming the data in A and B starts on row 1 and only contains the relevant
data,
then in C1 put
=SUBSTITUTE(OFFSET($A$1,INT((ROW(C1)-1)/COUNTA(B:B)),0),"^",D1)
and in D1 put
=OFFSET($B$1,MOD(ROW(D1)-1,COUNTA(B:B)),0)
and copy down until C returns blanks.
You can paste value into A and B if it's need in those columns.

kcc

Hank Rouse said:
I found this, however I need some help with it.

Again, I want to replace a special character "^" with the contents of
Column
B.

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(1)
End With

With Worksheets("Sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells
RngToChange.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub



Hank Rouse said:
Sorry, you missed the whole point of the loop through.



RagDyeR said:
You could make Column C into a "helper" column and enter this in C1:

=SUBSTITUTE(A1,"^",B1)

And copy down as needed.

You could then delete the Text formula in Column C, and leave the data
behind by selecting Column C, right click and choose "Copy".
Right click again, and choose "PasteSpecial",
And click on "Values", then <OK>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Looking for a tool, or code that does the following.

Replace Special Character in Sheet 1, Column A. [using "^" as my special
character, remove the quotes. Could change if using something else
Click to expand...
would
be
better.]

Replace ^ with contents of Sheet 1, Column B.

For example I have:

Column A Column B

12^798 xyz
0^5131 abc
5296^4 efg

Column A represents item #'s out of our catalogue.
Column B represents Supplier

So in the end I would get:

Column A Column B

12xyz798 xyz
12abc798 abc
12efg798 efg
0zyx5131 xyz
0abc5131 abc
0efg5131 efg
5296xyz4 xyz
5296abc4 abc
5296efg4 efg

Presently there are 59 criteria in Column B and I have to perform this
against 19 sites. So doing this manual via Find and Replace takes forever.

VBA code, or something similar would be great.

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find and Replace against set of rules in 2nd table/worksheet 7
Find values of a column in another and paste range of rows 1
Find and Replace against set of rules in 2nd table 13
Find and Replace against set of rules in 2nd table 4

Top