+ Reply to Thread
Results 1 to 11 of 11

Find and Replace Problem

  1. #1
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Find and Replace Problem

    Hello everyone and thank you for reading my post.

    Here is my situation and what I am attempting to accomplish.

    I have a sheet with somewhere around 2700 numbers that need to be found and replaced across 4 columns.

    Example of the numbers, left is the Find and the right is the Replace.

    34000 to be found 29232 to replace 34000 in sequential order 2700 times.

    So essentially across the 4 columns I need to find that left number and replace with the right number, 2700 specific numbers in total.

    How can I use a formula to do this at one time rather than spending an entire day using the basic Find and Replace function.

    Thanks, RC.
    Last edited by regularcat; 04-15-2018 at 08:46 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find and Replace Problem

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Find and Replace Problem

    Thank you Mr. Kennedy for your quick reply, it is appreciated.

    Attached is the worksheet you have asked for.

    The worksheet reads column A is what needs to Found, column B is what is to replace column A, column c1, c2, c3, c4 are where the replacing needs to happen.
    All data is relevant to the data in the worksheet, absolutely no false data.
    Attached Files Attached Files
    Last edited by regularcat; 04-15-2018 at 09:06 AM. Reason: Attempting to attach a file.

  4. #4
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Find and Replace Problem

    I have followed your instructions to attach the worksheet but it seems as though it is not working.

    -Edit-

    I have managed to attach the file, I apologize for the double post.
    Last edited by regularcat; 04-15-2018 at 09:07 AM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find and Replace Problem

    Your "sample" file is ENORMOUS. I did NO manual checking, but added a couple of values (in yellow) as a check. This seems to work. In I2, copied across and down:

    =IFERROR(INDEX($B$2:$B$2740,MATCH(D2,$A$2:$A$2740,0)),D2)
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Find and Replace Problem

    Thank you, I will give it a shot.

    I wish I knew a bit more about the formulas and what they do in order to do these things myself.
    No matter how much I read up on Excel I just can't ever seem to do what I need to do.

    I will let you know how everything goes.

  7. #7
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Find and Replace Problem

    Mr. Kennedy it does appear to have done just what I needed, thank you.

    If you have the time and do not mind would please breakdown the formula so that I can understand it so if this needs to be done again
    I won't have to bother you again.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find and Replace Problem

    Sure.

    =IFERROR(INDEX($B$2:$B$2740,MATCH(D2,$A$2:$A$2740,0)),D2)

    Red: Take the value in this cell (D2)
    Orange: Look for its EXACT...
    Cyan: match in this range, (MATCH returns the row number)
    Blue: return the corresponding value from this range.
    Black: if there is no match, then simply return the value in D2.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Find and Replace Problem

    crossposted: https://www.mrexcel.com/forum/excel-...lace-help.html

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  10. #10
    Registered User
    Join Date
    01-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Find and Replace Problem

    I got it, simple to understand.

    Thanks once again and reputation has been added.
    Your time and knowledge are very much appreciated and respected.

  11. #11
    Registered User
    Join Date
    04-19-2018
    Location
    New York
    MS-Off Ver
    2007
    Posts
    4

    Re: Find and Replace Problem

    Great! Was jut lokoing for this answer!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find and replace problem
    By goldie.mctraveller in forum Excel General
    Replies: 1
    Last Post: 06-14-2012, 10:11 PM
  2. [SOLVED] A find and replace problem
    By denemeler01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-18-2012, 02:53 PM
  3. [SOLVED] Find and Replace problem
    By Lolly in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-28-2006, 11:30 PM
  4. [SOLVED] Find-Replace problem
    By nastech in forum Excel General
    Replies: 1
    Last Post: 02-12-2006, 01:25 AM
  5. [SOLVED] Find replace problem
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: 12-08-2005, 06:35 PM
  6. Problem with Find and Replace
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 05:05 PM
  7. Problem with Find and Replace
    By mjhill01 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. Problem with Find and Replace
    By mjhill01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-18-2005, 12:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1