Skip navigation

how do you find and replace?

862 Views 22 Replies Latest reply: May 11, 2012 6:26 AM by susannePA RSS
1 2 Previous Next
susannePA Calculating status...
Currently Being Moderated
May 8, 2012 2:51 PM

How do you find and replace?

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    May 8, 2012 4:04 PM (in response to susannePA)

    Susanne,

     

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

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    May 8, 2012 5:52 PM (in response to susannePA)

    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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    May 8, 2012 6:53 PM (in response to susannePA)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 8, 2012 9:56 PM (in response to susannePA)

    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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    May 9, 2012 4:54 AM (in response to susannePA)

    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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    May 9, 2012 4:17 PM (in response to susannePA)

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 10, 2012 2:14 PM (in response to susannePA)

    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

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.