+ Reply to Thread
Results 1 to 8 of 8

Find & replace recursive

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    aus
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Find & replace recursive

    Hello all,

    I'm rather new here, so apologies if this is a rather silly question. I did search through previous posts but cant find anything that's similar to what I'm trying to do.

    I have 2 sheets in a workbook

    Sheet 1 - Product - has 2 columns. column A contains a Product Code and Column B contains Product Name (400 rows)
    Sheet 2 - Customer purchases - contains 3 columns. Column A contains customer id, column B contains customer name, and Column C contains Product Name.

    What I'm wanting to do is to replace the product name in Sheet B with the related product code in Sheet 1.


    So is there a macro that could recursivey use the data from sheet 1 to replace the data in sheet 2? So it would go through each row in sheet one use the data to do a find replace in sheet 2

    I would be most grateful for any help provided.

    Cheers

    Michael

  2. #2
    Registered User
    Join Date
    04-20-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Find & replace recursive

    Not sure a Macro would help here, unless you need it to trigger on a particular action. It sounds like you need to do a VLOOKUP between the two columns, so in the Column C it would looks something like =VLOOKUP($C125,'Sheet 1'!$A2:$B400,2,FALSE)

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Find & replace recursive

    You dont need a macro for that. Use in Column 'D'

    =INDEX(Sheet1!A:B,match(C2,Sheet1!B:B,0),0) and drag down
    Last edited by Ace_XL; 07-12-2012 at 08:58 AM.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    07-12-2012
    Location
    aus
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Find & replace recursive

    thanks to both of you for your assistance

    i've tried using the formula provided by ace - but when I paste in column d in sheet 2 I get the following error: #NAME?

    AM I doing something wrong?

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Find & replace recursive

    Isnt' this Sheet1 called named 'Product'?

    In that case, use

    =INDEX(Product!A:B,MATCH(C2,Product!B:B,0),0)

  6. #6
    Registered User
    Join Date
    07-16-2012
    Location
    asdasdasdasd
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Find & replace recursive

    Hello, I HAVE similar problem in some way. i havnet tested proposed solutions but used LOOKUP function and MATCH function. Problem is they sometimes give wrong results (possibly because they give similar result if they cant find exact match. I need to compare text strings and to give coresponding cell value only if text exactly matches what is needed.

    THE PROBLEM accoding to excel help

    =LOOKUP(4.19, A2:A6, B2:B6) Looks up 4.19 in column A, and returns the value from column B that is in the same row. orange
    =LOOKUP(5.00, A2:A6, B2:B6) Looks up 5.00 in column A, matches the next smallest value (4.19), and returns the value from column B that is in the same row. orange
    =LOOKUP(7.66, A2:A6, B2:B6) Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that is in the same row. blue
    =LOOKUP(0, A2:A6, B2:B6) Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A7. #N/A

    Similar problem is using MATCH function...
    Due to this i get wrong results...Do you have any ideas?

  7. #7
    Registered User
    Join Date
    07-16-2012
    Location
    asdasdasdasd
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Find & replace recursive

    my FIRST SHEET

    Product Code Product Name
    prodkods1 lineoone
    prodkods2 linetwo
    prodkods3 linetrhree
    prodkod4 linefour
    prodkod5 linefive

    mY sECOND SHEET
    customer id customer name Product Name MY RESULT
    234 SDAFSDFSDF lineoone prodkods1
    235 SDFSDF linetwo prodkod5
    567 DFGa linetrhree prodkods3
    456 SFDGSD linefour #N/A
    56745 linefive #N/A
    345345345 SDFSDF TILSA prodkod5

    =LOOKUP(C2,Sheet1!$B$2:$B$6,Sheet1!$A$2:$A$6)



    I have tested these functions and they are are totally useless

  8. #8
    Registered User
    Join Date
    07-16-2012
    Location
    asdasdasdasd
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Find & replace recursive

    i found solution using VLOOKUP and using FALSE as fourth argument. that works. only limitation seems to be that all colons must be in one sheet

+ 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