how do you find and replace?
How do you find and replace?
Susanne,
Say more about your situation. The question is too vague to know what direction to go with the answer.
Jerry
22 replies
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.
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:
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
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
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
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.
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.
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
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
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
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
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.
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
Is there a shorthand way to incidate the entire column as this sheet has over 4,000 rows?
Also - doesn't the column designation refer to the whole column and not just the cell it is in?
susannePA wrote: Also - doesn't the column designation refer to the whole column and not just the cell it is in?
Numbers will interpret partial references as appropriate.
In LOOKUP(R,F,Q)
R: requires a specific, single search value, so this will be interpreted as a reference to Rn, where n is the same row as the formula occupies.
F: requires a range of cells, so F will be interpreted as a reference to all cells in column F.
Q: requires a range of cells (equal to the range for the second argument, F, so Q will be interpreted as a reference to all cells in column Q.
Regards,
Barry
how do you find and replace?