Find and Replace against set of rules in 2nd column

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


0
1/15/2006 11:52:39 PM
excel 39879 articles. 2 followers. Follow

4 Replies
495 Views

Similar Articles

[PageSpeed] 29

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@hotmail.com> wrote in message
news:bfByf.3038$F01.1160@newssvr27.news.prodigy.net...
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



0
ragdyer1 (4060)
1/16/2006 12:51:00 AM
Sorry, you missed the whole point of the loop through.



"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:%231ZktbjGGHA.3896@TK2MSFTNGP15.phx.gbl...
> 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@hotmail.com> wrote in message
> news:bfByf.3038$F01.1160@newssvr27.news.prodigy.net...
> 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
>
>
>


0
1/16/2006 10:37:29 AM
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@hotmail.com> wrote in message
news:JHKyf.7902$Jd.7676@newssvr25.news.prodigy.net...
> Sorry, you missed the whole point of the loop through.
>
>
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:%231ZktbjGGHA.3896@TK2MSFTNGP15.phx.gbl...
> > 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@hotmail.com> wrote in message
> > news:bfByf.3038$F01.1160@newssvr27.news.prodigy.net...
> > 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
> >
> >
> >
>
>


0
1/16/2006 11:19:58 AM
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@hotmail.com> wrote in message 
news:yjLyf.8257$Jd.1778@newssvr25.news.prodigy.net...
>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@hotmail.com> wrote in message
> news:JHKyf.7902$Jd.7676@newssvr25.news.prodigy.net...
>> Sorry, you missed the whole point of the loop through.
>>
>>
>>
>> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
>> news:%231ZktbjGGHA.3896@TK2MSFTNGP15.phx.gbl...
>> > 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@hotmail.com> wrote in message
>> > news:bfByf.3038$F01.1160@newssvr27.news.prodigy.net...
>> > 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
>> >
>> >
>> >
>>
>>
>
> 


0
1/17/2006 2:57:05 AM
Reply:

Similar Artilces:

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Find Duplicate names and delate
Dear experts, I have a small doubt could you clarify that??? That is I find duplicate name but I want to delete one name only, if I filter DUPLICATE….. both names are showing… 1. Select the range of data including the header. You need to have headers for these columns 2. From menu Data>Filter>Advanced Filter>Copy to another location 3. In 'copy to' specify the target cell and check 'Unique records only' 4. Click OK will give you the unique list -- Jacob "Find Duplicate names and delate" wrote: > Dear experts, > I have a small ...

default setting of zoom slider in Outlook 2010
Love the new Outlook, but for some reason my default reading pane zoom slider setting is now at 170%. While I can change back to 100% to read message, each new message reverts back to 170%. Where can I change that globally? Thanks. JIM in Fla I would LOVE to figure this one out too! It's either 170% or 210 %, and I set it and it keeps resetting! Found the solution: http://social.technet.microsoft.com/Forums/en/office2010/thread/f8e2b566-593a-4ebf-833a-f7ae95d48bd9. You have to start a new mail message and changed the zoom in it to whatever zoom level you want to be the default (1...

Finding a Median
I'm trying to write a query that will return a median for various values taken from a previous query. I've seen some suggestions in my searching, but I haven't been able to get them working. They are also all from before 2003 and refer to Access 97 and 2000. Has any functionality been added to 2003 for this? Or is there a non- code-based way to do it? I've seen it suggested to write a code to open the query, sort it, find the total number of records, divide it in half, then seek out the middle record using that value. I'm still very green when it comes to code, though...

Find a Value the first Time It Occurs
I have a row of values that shows the total cumulative number of sales of items by month. Occasionally, there may be no sales in a month for an item so the cumulative value would stay the same for more than one month. I want to select a number in the row the first time it occurs and not select it if it repeats. What are you wanting to do with the info? To return position (column number) of number 1234 within row 2: =MATCH(1234,2:2,0) A formula that signals it's the first occurence: =COUNTIF($A2:A2,A2)=1 This could be used in a helper row, or as a conditional format f...

Find/Replace in RichEdit 2.0
I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my CRichEditDoc/View application so that I can use the ITextDocument interface and can do things such as suspend/resume the Redo buffer. Problem is, now the Find/Replace dialogs don't seem to do anything. If I revert back to RichEdit 1.0 they do! What's going on? BUMP! adriangibbons@yahoo.co.uk (Adrian Gibbons) wrote in message news:<34a2acd5.0404130713.123dbaab@posting.google.com>... > I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my > CRichEditDoc/View application...

Replacing data in Pivot Table causes Field name change
Good morning, I have a pivot table report that uses about 1200 rows of data on a neighboring page. Today I produced new monthly data and pasted it on top of the old data. When I do this the report's left most column field names all change from FieldName to FieldName2. When I look in the drop down box for that field it seems to have duplicated the fields when I pasted the new data. I'm able to work around it by changing the displayed lables from FieldName2 to FieldName"space""space". I can't change to just FieldName because that is already in use. M...

setting the same choice and order of the fields in all the folders
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C422DC.8D05E5E0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable How do I set the same choice and order of the fields in all the folders? For the moment the View settings apply to a particular folder and I have = to set them manually in every folder. ------=_NextPart_000_0008_01C422DC.8D05E5E0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <...

How do I set the default to "Open" or "New" for a template
Somehow I have changed a default setting that I cannot figure out how to change back. I have various templates setup in a "Forms" folder. Normally I go to My Documents, find the template file and double click on it, and the appropiate program (usually either Excel or Word) starts and opens a copy of the template as a document. What happens now is that the program starts and the template itself opens. I have tried going to My Documents and right clicking on the file and what appears to be happening is that the default (highlighted) option is "Open" instead of &quo...

Let's pick a replacement for this (Access) newsgroup 2
There were two big threads on the original 6/1/10 post in the General Questions and New User sections. The gist of the original post was: Folks have recommended various sites to replace this (Access) newsgroup for when poor-listener Microsoft drops it. Why don't we pick a replacement to give it the "critical mass" to fully replace this one? If interested, please respond to this with your recommendation. Also, if you feel like it, in case this goes dead, send me an email with your email address at North9000 at gmail dot com and I'll try to collect / send out...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message news:1184612089.486737.144020@n60g2000hse.googlegroups.com... > My program takes a name from sheet3 goes to sheet1 to Find the na...

Table Row Height and Column Width
Is there a way to exactly set the row height and column width in Publisher 2003? For instance, I want all the rows to be .25 inches high or 16 points high -- can I set this? No... You could create ruler guides. Right-click a ruler guide, click format ruler guides. You then can adjust your table rows by snapping to the guides. -- Mary Sauer http://msauer.mvps.org/ "Lori T" <Lori T@discussions.microsoft.com> wrote in message news:2631297F-839A-4699-B4F6-45601B771E1A@microsoft.com... > Is there a way to exactly set the row height and column width in Publisher > 2003...

Finding an event
Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button, a new window (in same workbook) opens with a new sheet. My problem is that excel does not seem to have any events for close of window if there are multiple windows in a workbook. Can someone help Peter Peter, That triggers the Workbook_WindowActivate event, you can use that. HTH Bob "Peter Ostermann" wrote in message news:i9m5v8$7bv$02$1@news.t-online.com... Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Trouble with Rule Wizard
I don't know if anyone has have this problemm. But here is goes. I've set up a rule with Rule Wizard. The rule works the first time but when I go to run the rule again it does not work. When am I doing wrong. It runs the first time but no other after that. I got the rule searching for a specific name in the subject and its there..it should forward over to that person. But it only forwards over the first time not everytime. Please help with any suggestion ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages d...

Sum if Condition is Equal in Range Date and find column
I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

How to set up IRA Account in Money 2007
I am trying to set up my IRA in Money. It is through a bank, so there are not investment type transactions, just the bank's own listings of interest. Do I set this up as an investment account or something different? For best treatment by things like Tax Estimator, I'd set it up as an Investment (IRA) account. You may never have any Investments in the Investment half of the account. It may just have interest earnings in the Investment Cash Account. This essentially wastes the Investment Account, but that's probably not a big deal. "Rick" <Rick@discussions.micr...

Changing column names
Can the columns be named ( changing the A B C etcto the months of the year? Cathy No. The column headings are either A,B,C or 1,2,3. You can hide them though, and use Row 1 as your column header row. Then you use whatever you want for the column headers. HTH Otto "Cathy S" <Cathy S@discussions.microsoft.com> wrote in message news:C6DB1DB3-7ED9-4AAD-8395-E9754DCEBA2C@microsoft.com... > Can the columns be named ( changing the A B C etcto the months of the > year? ...

Outlook 2003: No "AND" "OR" operator in message rules
I just upgraded from Outlook Express to Outlook 2003, and found this issue to be particularly bothersome. In Outlook Express, I could save time by using the "OR" operator when I make rules where an action takes place if the message matches any one of the criteria I list. However, when I imported those rules (and made new ones) in Outlook 2003, there is only an "AND" operator when I have several criteria. Thus my mail doesn't sort correctly. Is there any way to regain the "OR" functionality? I know it exists within each criteria (for example, if th...

WinFax replacement
I wrote an Access app years ago for a client and it uses WinFax pro to send out invoices. They need to upgrade but WinFax is gone thanks to the wisdom (LOL) of Symantec. Does anyone know of a good replacement that can be referenced from vba and will allow us to send out Access reports? Thanks, Keith I'm moving this to the microsoft.public.access newsgroup. There's not enough activity here for this to get any attention. If you see this here and have an answer, please answer it there. Thanks. "Keith G Hicks" <krh@comcast.net> wrote in messag...

option to insert column and row breaks
There should have an option to insert column breaks for printing. For example, when printing a sheet with only one column, but with 200 rows, it could be automatically broken into 4 columns of 50 rows, all fitting in just one page, instead of 4. This would save paper and allow easy reading of this kind of sheets. The same logic could be applied to rows... Thanks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see ...

how do i set up email in outlook 2003
Setting up email in outlook. Do I use my msn or aol email addresses? I have tried both and it tells me each time that files are not available offline. Please help. You cannot use Outlook for new MSN mail accts. Use your browser. You should not use it for AOL either. Use AOL's email program. If you insist on using Outlook with AOL, there's a workaround here: http://office.microsoft.com/en-us/assistance/HA010936921033.aspx "GG" wrote: > Setting up email in outlook. Do I use my msn or aol email addresses? I have > tried both and it tells me each time that fil...

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

How do I find the out of office reply?
My out of office reply is missing from tools. How can I retrieve it? Out of Office reply <Out of Office reply@discussions.microsoft.com> wrote: > My out of office reply is missing from tools. How can I retrieve it? Unless you are using an Exchange server, you will not have the Out of Office Assistant. See this: http://www.slipstick.com/rules/autoreply.htm -- Brian Tillman ...