1 2 Previous Next 22 Replies Latest reply: May 11, 2012 6:26 AM by susannePA
susannePA Level 1 Level 1 (0 points)

How do you find and replace?

  • 1. Re: how do you find and replace?
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    Susanne,

     

    Say more about your situation. The question is too vague to know what direction to go with the answer.

     

    Jerry

  • 2. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    I have a column with ID numbers.   I created a code that corresponds to the ID list, in another column.  On another worksheet, I have a column that contains the same IDs, in different order.  I want to replace the IDs in the second worksheet with the corresponding code from the first worksheet and put the coded IDs into a new column the second sheet. Then I plan to delete the original IDs leaving just the coded ones.

  • 3. Re: how do you find and replace?
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    Suzanne,

     

    There are several functions that you can use to lookup a value corresping to a known value. Let's begin with the most obvious, the LOOKUP function.

     

    Here's an example:

    Screen Shot 2012-05-08 at 8.45.45 pm.png

    It reads like this: Lookup the value in matching the one in column A of this row; look for it in CodeTable column A, and when you find it, return the value of CodeTable column B.

     

    Finally, you will have to dispose of the identities in in the Data table, but not before you freeze the disguised identities by doing the following: Click the Data table column B label to select that column and then Command-C, Edit > Paste Values. Then you can delete column a from the Data table.

     

    Jerry

  • 4. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    I need to be more specific or maybe I don't quite understand you:

     

    Table 1                               Table 2,                               Table 2

    col A        col B                        col A                                   col C

     

    ID 135      code1                    ID476                                   code 2

    ID 476      code2                    ID135                                   code 1

    ID 23       code 4                     ID17                                   code 9

     

    Table 1 has the codes, Table 2 needs to match IDs to fnd the codes from Table 1 and place the codes from column B into Column C.  So there are two tables with which I am working.

     

    Thank you, I think we're getting there and I'm so grateful.

     

    Susanne

  • 5. Re: how do you find and replace?
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    Apparently you didn't like my Codes and IDs. No matter how you approach this, you will have to mind the details. If you look for "ID 476", "ID476" won't be a match.

     

    You will, of course, have to substitute your specific table names and data for what I used in my example.

     

    Jerry

  • 6. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    Jerrry,  The fact tthat one of my tables has a space between the ID and the number is simply a careless oversight. There are no letters in any of my table, just numbers.  I will try your answer tomorrow and return to this page if it works.  If not, I'll be back to ask you more about it.  And, of course if it works, I'll check that you solved my problem. 

  • 7. Re: how do you find and replace?
    Barry Level 7 Level 7 (29,180 points)

    One issue with using LOOKUP is that it looks for a 'close match', defined as the 'largest value less than or equal to the search value.'

     

    This can lead to unexpected or unwanted results where an ID/code pair is missing from the data table, as displayed in rows 3, 5, 8 and 10 of the first 'Results' table below.

    Picture 8.png

    VLOOKUP, on the other hand, offers the option of accepting only an Exact match. Used alone, VLOOKUP will produce a "can't find" error and the error triangle shown in rows 3 and 5 of the second Results table.

     

    IFERROR can be added to the formula to act as an error trap, and produce whatever result you want to display for missing ID>Code paris in the Data table. An example is shown in rows 8 and 10 of the second table.

     

    Formulas:

    Result-Lookup table, C2 and filled down: =LOOKUP(A,Data :: $A,Data :: $B)

     

    Result-VLookup table, C2, filled down to C6:   =VLOOKUP(A,Data :: $A:$B,2,FALSE)

    Result-VLookup table, C7, filled down to C10: =IFERROR(VLOOKUP(A,Data :: $A:$B,2,FALSE),"Not found")

     

    Regards,

    Barry

  • 8. Re: how do you find and replace?
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    Susanne,

     

    Barry's comment and suggestion illustrates why your choice of function depends on the nature of your data and your objectives. My personal choice is normally to use the MATCH function in conjunction with either INDEX or OFFSET. MATCH gives you all the flexibility you could ask for, but it only returns a pointer to the row where the match is and you then have to use another addressing function to retrieve the desired data.

     

    Again, I began with the example of LOOKUP because of its simplicity. You should always think about what to do in the case of missing or mistakenly altered entries. Each test adds a layer of complexity, sometimes trivial and sometimes enough to slow the system. Nothing takes the place of a careful examination of the results.

     

    Jerry

  • 9. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    I used this formula

     

    =LOOKUP(A,Injury Data with ID codes :: F, Injury Data with ID codes :: Q)

     

    I got an error message saying that "F" isn't a valid reference.  The IDs are in column A of a table called, "Co-Morbidity orders."  I want to compare them with the IDs in column F in the table called, "Injury data with ID codes," and use the corresponding code from column Q in the table called, "Injury Data with ID codes."

     

    Why the error message?  Please help, I think we're almost there. 

     

    Susanne

  • 10. Re: how do you find and replace?
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    Susanne,

     

    I think that Numbers didn't recognize your table name in the formula. Try changing the table name from "Injury Data with ID codes" to "DataWithCodes", all one word, no spaces. Change both the table name and the formula's references to the table. Let us know what happens.

     

    Jerry

  • 11. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    I changed the table name to, "DataWith Codes" as you suggested, but got the same error message that F isn't a valid referrence.  I igured out that the problem is that I am referring to two separate tables, not separate sheets on the same table.  So I copied the second sheet next to the first creating one table.  And I used the formula you gave me:  =LOOKUP(R,F,Q) i.e. match the cells in column R with those in column F and put the result from column Q into the cells on the column with the formula.  However, it only gives me the answer in the cell that contains the formula.  Why isn't it working all down the column?

     

    I keep thinking we're almost there and you've helped me enormously, so far.

  • 12. Re: how do you find and replace?
    Barry Level 7 Level 7 (29,180 points)

    Hi Susanne,

     

    "However, it only gives me the answer in the cell that contains the formula.  Why isn't it working all down the column?"

     

    Normal behaviour.

     

    You need a copy off the formula in each cell. Click on trhe cell containing the formula to select it, then click the small circle at the bottom right corner of the selection and drag it down to fill the formula into the rest of the column.

     

    Regards,

    Barry

  • 13. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    Is there a shorthand way to incidate the entire column as this sheet has over 4,000 rows?

  • 14. Re: how do you find and replace?
    susannePA Level 1 Level 1 (0 points)

    Also - doesn't the column designation refer to the whole column and not just the cell it is in?

1 2 Previous Next