Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a column that has 140K rows, but there are about 5K of blanks that I
need the contents of the row above it copied into it, but if there is already data there to just leave the existing data alone - do not change or make blank. It would take many hours to search and work each individually.. Help is there a possible formula that will work. Thanks! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Highlight those 140k cells, assuming the active cell is the first cell
with data in it. Then press F5 (GoTo) and in the pop-up click on Special and then Blanks. Then begin to enter a formula by typing = and then click on the cell immediately above the first blank cell and do CTRL-Enter. All those blanks will now contain a formula to take the value immediately above it. Highlight the column, click <copy, then Edit | Paste Special | Values (check) | OK then <Esc to fix the values of those formulae. Hope this helps. Pete On Sep 24, 1:09*am, JLD wrote: I have a column that has 140K rows, but there are about 5K of blanks that I need the contents of the row above it copied into it, but if there is already data there to just leave the existing data alone - do not change or make blank. It would take many hours to search and work each individually.. Help is there a possible formula that will work. Thanks! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
As you are using XL2007, then instead of Edit | Paste Special etc you
should right-click then Paste Special etc. Hope this helps. Pete On Sep 24, 1:26*am, Pete_UK wrote: Highlight those 140k cells, assuming the active cell is the first cell with data in it. Then press F5 (GoTo) and in the pop-up click on Special and then Blanks. Then begin to enter a formula by typing = and then click on the cell immediately above the first blank cell *and do CTRL-Enter. All those blanks will now contain a formula to take the value immediately above it. Highlight the column, click <copy, then Edit | Paste Special | Values (check) | OK then <Esc to fix the values of those formulae. Hope this helps. Pete On Sep 24, 1:09*am, JLD wrote: I have a column that has 140K rows, but there are about 5K of blanks that I need the contents of the row above it copied into it, but if there is already data there to just leave the existing data alone - do not change or make blank. It would take many hours to search and work each individually.. Help is there a possible formula that will work. Thanks!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions |