Re: Find and Replace against set of rules in 2nd column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <hank_top_sf@xxxxxxxxxxx> wrote in message
news:yjLyf.8257$Jd.1778@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>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" <hank_top_sf@xxxxxxxxxxx> wrote in message
> news:JHKyf.7902$Jd.7676@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> Sorry, you missed the whole point of the loop through.
>>
>>
>>
>> "RagDyeR" <ragdyer@xxxxxxxxxxxxx> wrote in message
>> news:%231ZktbjGGHA.3896@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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!
>> > =====================================================
>> >
>> > "Hank Rouse" <hank_top_sf@xxxxxxxxxxx> wrote in message
>> > news:bfByf.3038$F01.1160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > 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
>> >
>> >
>> >
>>
>>
>
>


.

Relevant Pages