Replace data in a column

 Okay, I have a column that has text in it. I want to change all these to the 
Identifying number that is found in a different table. So how do I do this? 
Example:
Current table has:
    A
1 First name
2 New first name
3 Another first name

Need to change the A column in first table to the correspoding column in 
this table:
   A          B
1 12        First name
2 15        New first name
3 22        Another first name

So after the switch either I need a new column in the current table so it 
look like the second column (there might be more then one match in the 
current table to the numbers in the 2nd table) Or just totally replace 
anything that matches Column B in to Column A. Does that make sense? 

So after combining current table should be:
    A
1 12
2 15
3 22

Thanks for any help!
            
0
Utf
11/23/2009 4:43:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
699 Views

Similar Articles

[PageSpeed] 35

Suppose you have the first table in Sheet1 ColA and your data table with 2 
columns is in Sheet2.

In ColB of sheet1 enter the below formula and copy down..This will return 
the corresponding number from sheet2. Once done you can select Sheet1 ColB 
copy>Paste special>Values>OK and then remove ColA 

=INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))

If this post helps click Yes
---------------
Jacob Skaria


"Amelia" wrote:

>  Okay, I have a column that has text in it. I want to change all these to the 
> Identifying number that is found in a different table. So how do I do this? 
> Example:
> Current table has:
>     A
> 1 First name
> 2 New first name
> 3 Another first name
> 
> Need to change the A column in first table to the correspoding column in 
> this table:
>    A          B
> 1 12        First name
> 2 15        New first name
> 3 22        Another first name
> 
> So after the switch either I need a new column in the current table so it 
> look like the second column (there might be more then one match in the 
> current table to the numbers in the 2nd table) Or just totally replace 
> anything that matches Column B in to Column A. Does that make sense? 
> 
> So after combining current table should be:
>     A
> 1 12
> 2 15
> 3 22
> 
> Thanks for any help!
>             
0
Utf
11/23/2009 4:53:04 PM
Reply:

Similar Artilces:

selecting data from a database
I'll try my best to explain what i I want to do.... I have a databse that contains a list of times recorded by athletes over various distances. I would like ot be able to run a macro that compiles a report by selecting personal best times by athletes name for the various distances. the database is continually added to, athletes times are constantly updated so refencing specific cells is not feasible. I currently sort the database by name, distance, best time for that distance so I can see an athletes history but need a method to extract the best times for each discipl...

Data amalgamation
I have a table of data, including columns for date, a code indicatin data source, and various analysis under headings. As follows: Date Source A B C 1/1/01 X 2 4 8 15/3/01 X 3 5 7 16/6/02 Y 12 1 9 I want a summary table that is driven by a (variable) date range amalgamating data by source and A,B,C. As follows: X Y Z Pre 30/6/01 - A 5 0 0 - B 9 0 0 - C 15 0 0 Post 30/6/01 - A ...

How to align the bottom lines of 2-column pages?
I have compiled a bi-lingual dictionary (left to right and right to left) using Word 2007; with the result that the bottom lines of a 2-column page cannot be aligned; understandably so. Now, I want to import the documents to Microsoft Publisher 2007, where I know that aligning those bottom lines together can be done - but how? Any help offered will be much appreciated! How are you planning to export the text from Word? How many columns per page? Will your dictionary be in booklet form? Are your definitions in two different Word files? -- Mary Sauer http://msauer.mvps.or...

Validate for Null value for multiple column
I have requirement to validate for Null value which compares Column A & B entry in excel sheet. I have tried by setting IgnoreBlank = False, but no success. How do I validate for Null value in either Column A 0r B. url:http://www.ureader.com/gp/1027-1.aspx ...

Changing ODBC Data Sources
All: I have an Excel 2002 workbook with a lot of sheets. Each of those connect to a table in a specific access 2000 database. I had to recreate the access database due to a hole in the security. I had to create a new workgroup file and a new .mdb. I have used the same name for the .mdb file, but a different one for the ..mdw file (it had a typo in it). Useless to say that now none of the sheets is refreshing the data and to manually change all of them is a mess. Is there any way I could change the reference to this new .mdw and .mdb file in order to avoid doing the queries again one ...

newbie Question: Data exchange between Dialog and my own data structure
HI, I'm designing a program which has many CDialog(as docking control bar) and user data structures. I want to do some data exchange on those. For example: //================================================================== class CConfigDlg : public CDialog { .... //many common control such as CEdit... } class CMyData{ .... //many data members... } //================================================================== When I initialize the dialog, or some data hase changed in working thread,I want to update UI in using UpdateData(false); When I made some modification on the UI control...

duplicated data
I've a bunch of names and membership code of my fellow home makers...So how do I run a clean up of all the duplicated names? I really don't want to go blurry eyed trying to clean up manually THANX!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Take a look at data>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom "a_evie" <a_evie.v9on0@excelforum-nospam.com> wrote in message news:a_evie.v9on0@...

Export data from tables to Excel
Hi I want help on exporting data from tables to Excel. Im using a form with a command button when button is pressed i want all the data on the form to goto an Excel spreadsheet but i want the spreadsheet to auto save with the primary key as the file name. Thank you Andy Andy, you can use a macro with transfer spreadsheet. In the macro you specify the name for the new file. It is auto saved. Jeanette Cunningham "Andysc" <u41622@uwe> wrote in message news:803b29d5bc08a@uwe... > Hi > I want help on exporting data from tables to Excel. > Im using a form with a comma...

how do I set my tab to stop at column N and rtn to A
I have an invoice created in excel that I would like to be able to tab over no further than the end of the invoice(column N) and then return to column A. Howeer, the tab continues past the column and I don't know how to lock it. TL, You can hide all the columns past column N, and the tab will stop at column. If youve been tabbing, from column A for example, and have arrived in column N, the Enter key will return you to column A, one row down. Otherwise, you can use the Home key to get back to column A, then the Enter key do go down one row. If that isn't good enough, you can...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Hiding / Displaying columns
Opened spreadsheet that has numbered tabs above row 1 that show / hide columns. Also shows + and - signs showing the related columns. Just wondering how to create this in a spreadsheet. Thanks... Select the columns (or rows) that you want to apply it to. Then Data>Group and Outline>Group. UnGroup to undo the operation Regards Rowan majesticaussies wrote: > Opened spreadsheet that has numbered tabs above row 1 that show / hide > columns. Also shows + and - signs showing the related columns. Just > wondering how to create this in a spreadsheet. Thanks... ...

Requesting data from Servers
This problem just started recently. "Requesting Data from Server" Machine running outlook 2000 Are you on an exchange server? "BJ" <anonymous@discussions.microsoft.com> wrote in message news:0ab001c3f494$88bb4fa0$3a01280a@phx.gbl... > This problem just started recently. "Requesting Data from > Server" Machine running outlook 2000 >-----Original Message----- >Are you on an exchange server? > >"BJ" <anonymous@discussions.microsoft.com> wrote in message >news:0ab001c3f494$88bb4fa0$3a01280a@phx.gbl... >> This pr...

retroactive deferral data to GL
Posting a Sales Retroactive Deferral does not populate the same columns in GL as if it were posted from Sales directly. This would be helpful for users and reports to have the data the same, regardless of it was retroactively entered or not. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Source Data / Series
I have 8 charts in one of my workbooks in one of my files. Every month, I change the source data (series) to include the most recent month for all of the series within the chart. For example, I change: {from} =Sheet1!$V$18:$V$600 { to } =Sheet1!$V$18:$V$650 Here is the dilemma: WHEN I AM FINISHED CHANGING ALL THE SERIES IN THE CHART, I PRESS 'OKAY'. Instead of my cursor remaining where it was before I edited the chart, i.e. somewhere near the chart, THE CURSOR JUMPS TO THE BEGINNING OF THE WO...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Word Mail Merge with Excel Data--Missing Data
I have created a mail merge document in Word2002, using Excel2002 for m data source. When I merge the document, some of my fields hav incomplete data. The only records affected are those where the dat from the Excel fields is fairly long. Only about 200 characters wil show up on the Word doc. I am not exceeding any limits in my Exce fields. I've tried everything I know to do, but can't get this one t budge. Any suggestions are welcome -- Message posted from http://www.ExcelForum.com The following posts by Word MVP, Cindy Meister, may help you: http://groups.google.com/grou...

how do I print data in a spreadsheet data base to a mailing lable.
I would lik eto take the data from a customer data base in Excell and print each customers information on a mailing lable can this be done? should I use Access? if it can be done please help me. I currently am using Microsoft Office 2000 You could use Word's mail merge feature to create the mailing labels. There's mail merge information on the Word MVP site: http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm And David McRitchie has instructions on his web site: http://www.mvps.org/dmcritchie/excel/mailmerg.htm Kevin Jamieson wrote: > I would lik eto ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Summing Data
I have an Excel Worksheet includes travel costs by employee by type of expense. These expense is allocate to cost centers based on the time each employee spends in the cost center. The worksheet lay out is: Col A Col B Col C Col E Col F Col G Col H Col I Employee Type of Exp Total exp 10-510 10-550 50-510 81-510 90-550 John Doe 5523 $500 $250 $50 $100 $0 $100 John Doe 5524 $250 $125 $25 $ 50 $0 $ 50 Mary Doe 5524 $...

Calculate weighted average for 1 column
Hi I 'm looking for ways to calculated weighted average for 1 column, random values as follows. I do not have frequences/quantities values. 1.3 1.4 1.6 1.7 1.7 1.8 1.6 1.6 1.7 1.6 What do you want to weight them by? Without a second variable, the only thing you can do is calculate the average, as in: =average(A:A) Regards, Fred "Jul2010" <Jul2010@discussions.microsoft.com> wrote in message news:27684926-5EC8-4A59-BC25-41220489D124@microsoft.com... > Hi > I 'm looking for ways to calculated weighted average for 1 column, random > ...

Split Data
Hi I have data imported from a text file when the data is copied to excel each records data is in the Column A. E.g A 1[12345, ,smith,daniel, , 12, simonds street] 2[12346, ,west,fred, , 67b, sutton street] I need to split the cells A B c d e f 1[12345][smith][daniel][][ 12] [simonds street] 2[12346][west] [fred] [][ 67b][sutton street] Is this at all possible. Thanks Simon Hi have a look at 'Data - Text to columns' and use the coma as separator >-----Original Message----- >Hi >...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...