filling in Gaps
Posted by Hassan Omar on August 06, 2001 7:23 AM
I need to find a way to automatically fill in the gaps in a column of numbers.
for example, I need to fill in the gaps in column 'a' only:
a1 b1
122 as
125 aa
sd
222 vc
222 ah
af
df
223 sf
I will need the formula to copy the value from the cell above gap and paste it to all cells in the gap until the next value is reached.
The result would be as follows:
a1 b1
122 as
125 aa
125 sd
222 vc
222 ah
222 af
222 df
223 sf
Posted by Hassan Omar on August 06, 2001 7:26 AM
Upon reviewing formatting of how the previous posting appears, it should be noted that all of the letters would originally be in column 'b'
Posted by Mark W. on August 06, 2001 7:47 AM
1. Select column A.
2. Choose the Edit | Go To... | Special... menu
command, select "Blanks" and press [ OK ].
3. Type the formula, =A2, and press Control+Enter.
4. Select column A once again.
5. Choose the Edit | Copy followed by
Edit | Paste Special... Values.
Posted by Hassan Omar on August 06, 2001 11:57 AM
Is there an easier way to do this? i.e. is there a way to use the column next the column 'a' with a formula(after inserting a column)? The spread sheet I have has a lot of gaps.
Posted by Hassan Omar on August 06, 2001 12:04 PM
That didn't work. I need it to copy the last valus at the beginning of the gap, and fill it down til the next value is encountered. for the next gap for it to do the same.
Posted by Aladin Akyurek on August 06, 2001 1:12 PM
Hassan,
I'll assume your data to in columns A and B.
Insert an empty row so that A1 and B1 are blank.
In C2 enter: =IF(ISNUMBER(A2),A2,IF(ISNUMBER(C1),C1,""))
Copy down as far as needed. When ready, select all cells of C from C2 on with a number in it, do a copy, activate A2 and do a Paste Special|Values (after verifying of course that all gaps have been filled up according to your specs).
Aladin
That didn't work. I need it to copy the last valus at the beginning of the gap, and fill it down til the next value is encountered. for the next gap for it to do the same. : The result would be as follows
Posted by Mark W. on August 06, 2001 2:55 PM
> That didn't work.
It does work! Is it possible that in your data set
that the first non-blank cell is something other
than A2? At step #2 type '=', click on the
cell containing 125, and then Control+Enter. This
will fill-in only the blank cells with a relative
reference to the cell above. I routinely use this
approach without any problems.
If you can't manage this then I'd recommend that
you get a copy of ASAP Utilities freeware at
www.asap-utilities.com and use it's
"Fill > Copy copy values to empty cells below
filled cells in selection" menu command. That didn't work. I need it to copy the last valus at the beginning of the gap, and fill it down til the next value is encountered. for the next gap for it to do the same. : The result would be as follows
Posted by Mark W. on August 06, 2001 2:57 PM
It doesn't get much easy than this. It fills in
all of the gaps simultaneously. You don't have
to address each gap individually. Is there an easier way to do this? i.e. is there a way to use the column next the column 'a' with a formula(after inserting a column)? The spread sheet I have has a lot of gaps.
