+ Reply to Thread
Results 1 to 8 of 8

Match Search & Replace with 2 spreadsheets

  1. #1
    Registered User
    Join Date
    03-09-2011
    Location
    utah, usa
    MS-Off Ver
    excel 2007
    Posts
    23

    Match Search & Replace with 2 spreadsheets

    Hi,

    I am looking to create a macro that will search 1 spreadsheet named input under column/rows b and match another spreadsheet called output on another file in column/rows i. If it matches then on input column/rows to grab the contents in cell d and I on the same row as the match and then copy/replace cell to output on the same row as the match to column j & k.

    I have attached a spreadsheet called Input and Output and on Output will have another tab called Results Example. I highlighted the search and match columns in yellow for Input & Output. I then highlighted in Red if the search & match columns match to replace the cells.

    The Tab on Output called Results Example I have what the final results should look like. The columns that are changed should be blue.

    Note: The Results Example tab is just an example not where I want it stored. The Results should be replacing column/rows on the output tab on j & k.

    Explaining it is difficult but I hope my spreadsheet examples helped out.

    Thanks for your help in advance
    Input.xlsxOutput.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Match Search & Replace with 2 spreadsheets

    Hi

    Check this:

    Input.xlsm

    Output.xlsx
    Click *, if my suggestion helps you. Have a good day!!

  3. #3
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Match Search & Replace with 2 spreadsheets

    Edit: Looks like jraj beat me to it and you're in good hands. Perhaps there is something here that might be helpful nonetheless. I went the direction of letting you navigate to the 'Input' file since its location on your machine is unknown.

    -as-

    Make copies of your workbooks and test on the copies. The following would be placed in a standard module within the 'Output' workbook and run via the 'Macros' button of the Developer tab. This code assumes that the 'Input' workbook is closed and resides on your hard drive.

    Please Login or Register  to view this content.
    Last edited by AlvaroSiza; 05-21-2012 at 01:40 AM. Reason: Didn't see previous post
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  4. #4
    Registered User
    Join Date
    03-09-2011
    Location
    utah, usa
    MS-Off Ver
    excel 2007
    Posts
    23

    Re: Match Search & Replace with 2 spreadsheets

    Hi,

    thanks for the responses.

    On the sample input and output tests work great, however i am trying it on the original worksheets. When i try it on the original worksheet i just get an hour glass. I am assuming that it is trying to match and it doesn't match. The source for range b1 (input) is the same on the original but the Output match column is column 10 so i changed that. The offset from the b1 source input is 2 which is on column d, and the other source input is 7 columns over which is column i. On the Output the match column is column 10 which is j, the replace columns are column j which is offset 1 from column k and column Q which is offset 7 from column j. I probably didn't do it right but this is what i changed below and it just has an hour glass?

    thanks

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Match Search & Replace with 2 spreadsheets

    Any chance you could dumb up the data in the actual files and supply? It's really tough to diagnose without it.

    Do you know how to 'step through' code? If so, please do so and report the results.

    If not, with the VBE open and the code visible, click the beginning sub line and press F8. You will see a yellow highlight appear. Each instance of F8 will move one line further through the code. We might be able to find out where it is 'hanging'.

  6. #6
    Registered User
    Join Date
    03-09-2011
    Location
    utah, usa
    MS-Off Ver
    excel 2007
    Posts
    23

    Re: Match Search & Replace with 2 spreadsheets

    Hi,

    Here is the Input & Output as you requested attached. I highlighted the columns like the other ones.

    thanks for your help
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Match Search & Replace with 2 spreadsheets

    Utah,

    I ran it without issue. The code does take a bit to process (+/- 10 sec on my machine; W7, O2007, Core i5, 4gb). My best guess is that you are on an older machine?

  8. #8
    Registered User
    Join Date
    03-09-2011
    Location
    utah, usa
    MS-Off Ver
    excel 2007
    Posts
    23

    Re: Match Search & Replace with 2 spreadsheets

    Its working fine now, i was testing it on a big file that took 5-10 min to complete, i couldn't put the entire input/output files there way over a meg which is the limit. I have two other easy questions. Is there a way to keep it in the same directory when opening the folder? Also, how would i add .txt with the xl files?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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