Replacing A Column Of Numbers

Hello,

New user of Excel 2007, and Excel in general.
Should be simple, but I just can't figure out how.

Have a column of numbers in Column A
In Column B (row 1)I have the formula of what's in A (row 1) multiplied 
by 1000

And I have dragged this formula downwards, so I now have in each row B
what is in the same row in Column A multiplied by 1000.

So far, all is fine.

Now, I want to replace what's in Column A by the newly generated values 
in Column B  (row by row replacement)

I can't just Cut out everything in Column A, as B then disappears, as, 
I've finally figured out, Column B values depends on Column A values.

How do I move everything I've generated in Column B to Replace what's in 
Column A ?

Much thanks,
Bob
0
Bob
3/14/2010 4:35:20 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1028 Views

Similar Articles

[PageSpeed] 16

On 14/03/2010 16:35, Bob wrote:
> Hello,
>
> New user of Excel 2007, and Excel in general.
> Should be simple, but I just can't figure out how.
>
> Have a column of numbers in Column A
> In Column B (row 1)I have the formula of what's in A (row 1) multiplied
> by 1000
>
> And I have dragged this formula downwards, so I now have in each row B
> what is in the same row in Column A multiplied by 1000.
>
> So far, all is fine.
>
> Now, I want to replace what's in Column A by the newly generated values
> in Column B (row by row replacement)
>
> I can't just Cut out everything in Column A, as B then disappears, as,
> I've finally figured out, Column B values depends on Column A values.
>
> How do I move everything I've generated in Column B to Replace what's in
> Column A ?
>
> Much thanks,
> Bob

Highlight Column B, right-click and choose Copy.
Highlight Column A, right-click, choose "Paste Special" and choose Values.

HTH
0
Gordon
3/14/2010 4:46:50 PM
Copy column B.

Select A1 and Edit>Paste Special>Values>OK>Esc.

You can miss a step if you wanted to.

You don't need formulas in column B.

Enter 1000 in B1 or any cell outside Column A

Copy that cell.

Select the range in column A and Edit>Paste Special>Multiply>OK>Esc.


Gord Dibben  MS Excel MVP

On Sun, 14 Mar 2010 12:35:20 -0400, Bob <rgsros@notme.invalid> wrote:

>Hello,
>
>New user of Excel 2007, and Excel in general.
>Should be simple, but I just can't figure out how.
>
>Have a column of numbers in Column A
>In Column B (row 1)I have the formula of what's in A (row 1) multiplied 
>by 1000
>
>And I have dragged this formula downwards, so I now have in each row B
>what is in the same row in Column A multiplied by 1000.
>
>So far, all is fine.
>
>Now, I want to replace what's in Column A by the newly generated values 
>in Column B  (row by row replacement)
>
>I can't just Cut out everything in Column A, as B then disappears, as, 
>I've finally figured out, Column B values depends on Column A values.
>
>How do I move everything I've generated in Column B to Replace what's in 
>Column A ?
>
>Much thanks,
>Bob

0
Gord
3/14/2010 4:50:32 PM
Reply:

Similar Artilces:

Access/Excel: Create multiple Lines/Column out of one mulit-line Column?
Hallo everyone! I spent a lot of time today searching for a response to my question but I had unfortunately no success. The problem is the following: I have got an Access-table called Employee with an ID and one corresponding Field/Column called comment. These comments are stupidly multi-line (separated by a Return). It's my aim to have access (he he, wordplay) to these two fields in Excel whereas the field shouldn't be longer that 255 characters. I considered three solutions: The table could be changed after being exported to excel in the following two ways: - Multiple Comment Column...

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...

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

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...

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...

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...

Serial number in Queries
I am just wondering how to make the serial number through query, and it's not related with any data or table Means once I run query there is data will be show and I want to be serial number shows in the first of each raw. Ken Create a table that has the fields matching your query, plus an AutoNumber. Turn it into an Append query, and append the data to the table. The AutoNumber field gives you the sequence. If you need to programmatically reset the seed of the AutoNumber after you delete the data, see: http://allenbrowne.com/func-ADOX.html#ResetSeed -- Allen Browne - Microsof...

MS Money 2004 & large Neg Numbers
I installed the Trial this morning and loaded a converted Quicken 2003 file. It worked fine for a bit. Then all of a sudden, my home screen and Cash flow projected etc. screens show extremely LARGE negative balances (in the zillions) ;-) I cannot find any entry for these large negative numbers. Anyone seen this? Is there a setting I have mistakenly changed? thanks Doug In microsoft.public.money, ItsDouglas wrote: >I installed the Trial this morning and loaded a converted >Quicken 2003 file. It worked fine for a bit. Then all of a >sudden, my home screen and Cash flow pr...

How do I combine multiple columns into just 1 column?
In my excel file, the address is split into 4 columns. I want to combine the 4 columns into 1. In a fifth column, use the concanenate function or amphisand to combine the information. Then drag down to copy for each row. =CONCATENATE(A1,B1,C1,D1) or =A1&B1&C1&D1 HTH "scosus" wrote: > In my excel file, the address is split into 4 columns. I want to combine the > 4 columns into 1. ...

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... ...

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: > ...

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...

optimum method to print rows as columns
looking to convert each spreadsheet row to print as a column per page -- Concord If you've got more than one or two rows to deal with, the optimum is probably the macro below. You'll need to change the names of the two worksheets involved, and the "EmptySheet" referred to may even have to be added to your workbook if all the sheets in it are currently being used for something. To put the code to work: Start by making a copy of your workbook to test things with. Then open that copy and press [Alt]+[F11] and choose Insert-->Module and then copy and paste...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Format Column
I have a column with inmate id numbers in it. I imported them from a text based program. I made a custom formatting for the column because all the id numbers begin with zero. IE 00112356 or 01555666 etc... The format I used was 00000000 under custom. The problem now is that I want to import them into access but access doesn't see the zero at the beginning. How can I make it show the literal number including the zeros? I tried adding the '01222555 before the number, but I would have to manually append 2000 records. Is there a faster way? Morph. Hi you could use a helper column...

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...

change column names to letters
Normally column headers have names using letters and row header has names using numerals. In my excel numerals are used to define both columns & rows headers. Normally you have A1 cell, and I have R1C1. How can I change to normal headers, so that Columns header would consist of letters, and rows header consist of numerals? Thank you very much. Tools>Options>General, uncheck R1C1 Reference style -- Kind Regards, Niek Otten Microsoft MVP - Excel "Raimundas Dundulis" <anonymous@discussions.microsoft.com> wrote in message news:01e601c3a7b9$657d6190$a301280a@phx....

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Excess columns
I have set up my worksheet, but have a tremendous number of columns left unused. I have tried to delete them, but they don't go away. How can I get rid of them? Karen, you can not, but you can hide them ,select them and format columns hide -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Karen" <Karen@discussions.microsoft.com> wrote in message news:594ED2CD-74A9-4356-BEC7-787D45029470@microsoft.com... >I...

Numbering for column
Hi, Good Day! I seldom used microsoft excel but sometimes i do used it for some simple calculation. When I just opened an excel spreadsheet, the numbering of column is in number format(1, 2, 3...) and not in alphabet format(A, B, C...) May I know how can i reset it back to alphabet? Thanks in advanced. rgds, Phoebe Tools|Options|General|Uncheck R1C1 Reference Style (this is picked up from the first workbook you open in that session. So make sure you save your workbook after the change--and make a note of how you fixed it. It can happen again if the first workbook opened has R1C1 refe...

Inventory item numbers
There are quantities for existing item numbers. What is the best method to move these balances to new item numbers: 1) Buy item number modifier from Professional Tools or 2) Use decrease adjustments to reduce the existing balances to zero, then post increase adjustments to the new item numbers. Jackie, Is there any reason that you would want to keep the history of what happened under the old number vs. the new one? If so, then go with option 2. Will the new item be set up differently than the old one? If so, most likely you'll want option 2 (this will be true for most 'major...

Numbering Add-In #2
I have a purchase order template I created with Excel 97 which includes an automatic counter field to give each PO its unique ID number. When I try to open this template in XP, I am told that I need to install the numbering add-in into the Library. However, I cannot find this add-in amongst those available in Excel. I have had no luck trying to source it on the Microsoft website, find reference to this specific add-in in Offline or Online help etc. WHERE IS IT???? This add-in is no longer part of Office: http://support.microsoft.com/default.aspx?scid=kb;en-us;288118&Product=xlw ...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Group header page numbering
Hello All Access Guru out there Would like to have some guide / code / example on how to reset a page numbering to 1 of each group? ie Total pages = 6 Group Pages Site 1 1 / 1 Site 2 1 / 1 Site 3 1 / 2 Site 3 2 / 2 Site 4 1 / 1 Site 5 1 / 1 thaks in advance Hi, Try this (But it's in french) http://officesystemaccess.seneque.net/ex_pagination_groupe.htm TopJB PWYS a �crit : > Hello All Access Guru out there > > Would like to have some guide / code / example on how to reset a page > numbering to 1 of each group?...