how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One
of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
Jim,
Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
Thanks, this is helpful up to the last sentence; Then copy
the whole column and paste special values. At this point I'm lost, I mean how do I get Excel to repeat this process through the remaining 13,000 rows? Also, more often than not I don't have a single empty cell, but a number, up to about 5 or 6. So when there are multiple cells I want all of them populated with the contents of the next populated cell. "Bernie Deitrick" wrote: Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
When Bernie typed 'Select the column' he meant to click on the column header
- i.e., the 'A' above column A. This will select the entire column. Once you've done that, follow the rest of his steps to fill ALL empty cells "Jim99" wrote: Thanks, this is helpful up to the last sentence; Then copy the whole column and paste special values. At this point I'm lost, I mean how do I get Excel to repeat this process through the remaining 13,000 rows? Also, more often than not I don't have a single empty cell, but a number, up to about 5 or 6. So when there are multiple cells I want all of them populated with the contents of the next populated cell. "Bernie Deitrick" wrote: Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
Yes, I did click on the column header to select the whole column. then
editgo tospecialblanks and Excel then drops to the next empty cell. If the cell below the next empty cell is empty then this action populates the identified cell with < 0 , zero. If I keep tapping the down arrow till I get to a populated cell, Excel fills in the identified cell, but only the identified cell. So I have to execute this sequence for each empty cell. This tactic takes longer than myself selecting the next populated cell and dragging its contents upward to populate the empty ones. At this point it seems like I can only get around this problem, because the sheet is so big, by learning Access. I have two other big worksheets involved in this task. I was hoping to just splice them together in Excel, which would require no empty cells. "Duke Carey" wrote: When Bernie typed 'Select the column' he meant to click on the column header - i.e., the 'A' above column A. This will select the entire column. Once you've done that, follow the rest of his steps to fill ALL empty cells "Jim99" wrote: Thanks, this is helpful up to the last sentence; Then copy the whole column and paste special values. At this point I'm lost, I mean how do I get Excel to repeat this process through the remaining 13,000 rows? Also, more often than not I don't have a single empty cell, but a number, up to about 5 or 6. So when there are multiple cells I want all of them populated with the contents of the next populated cell. "Bernie Deitrick" wrote: Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
Jim,
Excel appears to drop to the next empty cell, but it actually has selected ALL the empty cells. The first empty cell is the active cell. Do not touch anything except the = key after hitting OK on the Go To / special / blanks. Then press the down arrow once, and press CTRL-ENTER. That is the crucial step - it appears that you only pressed enter. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... Yes, I did click on the column header to select the whole column. then editgo tospecialblanks and Excel then drops to the next empty cell. If the cell below the next empty cell is empty then this action populates the identified cell with < 0 , zero. If I keep tapping the down arrow till I get to a populated cell, Excel fills in the identified cell, but only the identified cell. So I have to execute this sequence for each empty cell. This tactic takes longer than myself selecting the next populated cell and dragging its contents upward to populate the empty ones. At this point it seems like I can only get around this problem, because the sheet is so big, by learning Access. I have two other big worksheets involved in this task. I was hoping to just splice them together in Excel, which would require no empty cells. "Duke Carey" wrote: When Bernie typed 'Select the column' he meant to click on the column header - i.e., the 'A' above column A. This will select the entire column. Once you've done that, follow the rest of his steps to fill ALL empty cells "Jim99" wrote: Thanks, this is helpful up to the last sentence; Then copy the whole column and paste special values. At this point I'm lost, I mean how do I get Excel to repeat this process through the remaining 13,000 rows? Also, more often than not I don't have a single empty cell, but a number, up to about 5 or 6. So when there are multiple cells I want all of them populated with the contents of the next populated cell. "Bernie Deitrick" wrote: Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
Oh my gosh! magic! wow, thank you so much. of course I feel stupid,
because you told me contol-enter the first time. whew, another month I can put off learning Access, calculus, quadratic equations and my parent's new remote. Thanks. "Bernie Deitrick" wrote: Jim, Excel appears to drop to the next empty cell, but it actually has selected ALL the empty cells. The first empty cell is the active cell. Do not touch anything except the = key after hitting OK on the Go To / special / blanks. Then press the down arrow once, and press CTRL-ENTER. That is the crucial step - it appears that you only pressed enter. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... Yes, I did click on the column header to select the whole column. then editgo tospecialblanks and Excel then drops to the next empty cell. If the cell below the next empty cell is empty then this action populates the identified cell with < 0 , zero. If I keep tapping the down arrow till I get to a populated cell, Excel fills in the identified cell, but only the identified cell. So I have to execute this sequence for each empty cell. This tactic takes longer than myself selecting the next populated cell and dragging its contents upward to populate the empty ones. At this point it seems like I can only get around this problem, because the sheet is so big, by learning Access. I have two other big worksheets involved in this task. I was hoping to just splice them together in Excel, which would require no empty cells. "Duke Carey" wrote: When Bernie typed 'Select the column' he meant to click on the column header - i.e., the 'A' above column A. This will select the entire column. Once you've done that, follow the rest of his steps to fill ALL empty cells "Jim99" wrote: Thanks, this is helpful up to the last sentence; Then copy the whole column and paste special values. At this point I'm lost, I mean how do I get Excel to repeat this process through the remaining 13,000 rows? Also, more often than not I don't have a single empty cell, but a number, up to about 5 or 6. So when there are multiple cells I want all of them populated with the contents of the next populated cell. "Bernie Deitrick" wrote: Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow key once, and press Ctrl-Enter. Then copy the whole column and paste special values. HTH, Bernie MS Excel MVP "Jim99" wrote in message ... I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks |
All times are GMT +1. The time now is 11:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com