Complex Fill
 

Is there a single command that will fill each blank cell below a cell
with a number with that same number? There are 435 numbers and
different numbers of blank cells (including none) below each number.
A portion of the table is shown below. I want, e.g., the cell below 1
to show 1, the two cells below 2 to show 2, the two cells below 3 to
show 3 - and because there is no blank below four, move on to the next
1, etc.

Ken

AR 1 BERRY, MARION
AR HUMPHREY, VERNON WOO
AR 2 GARNER, ED
AR PARKS, MARVIN
AR SNYDER, VICTOR F
AR 3 BOOZMAN, JOHN NICHOL
AR JUDY, JANICE (JAN) A
AR MORFEY, DALE WILLIAM
AR 4 ROSS, MICHAEL A
CA 1 MEYERS, WILLIAM P
CA THOMPSON, MIKE
CA WIESNER, LAWRENCE R
CA 2 HERGER, WALTER WILLI
CA VANCE, JEFFREY PAUL
CA 3 CASTILLO, GABRIEL E
CA LUNGREN, DANIEL E
CA OLLER, THOMAS R
CA OSE, MARY
CA TUMA, DOUGLAS ARTHUR

Complex Fill
 

Hi
if you have these numbers in column A try the following:
- select column A
- hit F5 and choose 'Special'
- select 'Blank cells'
- now enter the equation sign '='
- hit the upper arrow key
- hit CTRL+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany


Ken wrote:
Is there a single command that will fill each blank cell below a cell
with a number with that same number? There are 435 numbers and
different numbers of blank cells (including none) below each number.
A portion of the table is shown below. I want, e.g., the cell below
1
to show 1, the two cells below 2 to show 2, the two cells below 3 to
show 3 - and because there is no blank below four, move on to the
next
1, etc.

Ken

AR 1 BERRY, MARION
AR HUMPHREY, VERNON WOO
AR 2 GARNER, ED
AR PARKS, MARVIN
AR SNYDER, VICTOR F
AR 3 BOOZMAN, JOHN NICHOL
AR JUDY, JANICE (JAN) A
AR MORFEY, DALE WILLIAM
AR 4 ROSS, MICHAEL A
CA 1 MEYERS, WILLIAM P
CA THOMPSON, MIKE
CA WIESNER, LAWRENCE R
CA 2 HERGER, WALTER WILLI
CA VANCE, JEFFREY PAUL
CA 3 CASTILLO, GABRIEL E
CA LUNGREN, DANIEL E
CA OLLER, THOMAS R
CA OSE, MARY
CA TUMA, DOUGLAS ARTHUR

Complex Fill
 

Perfect hit Frank...

And Do not forget to Copy = Paste Special - Values so that the
formulas are no longer required.

Just an additional step so that the data integrity is maintained if
you sort or do some operation on the data !!!

Thanks,.

Yogendra

Frank Kabel wrote:
Hi
if you have these numbers in column A try the following:
- select column A
- hit F5 and choose 'Special'
- select 'Blank cells'
- now enter the equation sign '='
- hit the upper arrow key
- hit CTRL+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany


Ken wrote:

Is there a single command that will fill each blank cell below a cell
with a number with that same number? There are 435 numbers and
different numbers of blank cells (including none) below each number.
A portion of the table is shown below. I want, e.g., the cell below

1

to show 1, the two cells below 2 to show 2, the two cells below 3 to
show 3 - and because there is no blank below four, move on to the

next

1, etc.

Ken

AR 1 BERRY, MARION
AR HUMPHREY, VERNON WOO
AR 2 GARNER, ED
AR PARKS, MARVIN
AR SNYDER, VICTOR F
AR 3 BOOZMAN, JOHN NICHOL
AR JUDY, JANICE (JAN) A
AR MORFEY, DALE WILLIAM
AR 4 ROSS, MICHAEL A
CA 1 MEYERS, WILLIAM P
CA THOMPSON, MIKE
CA WIESNER, LAWRENCE R
CA 2 HERGER, WALTER WILLI
CA VANCE, JEFFREY PAUL
CA 3 CASTILLO, GABRIEL E
CA LUNGREN, DANIEL E
CA OLLER, THOMAS R
CA OSE, MARY
CA TUMA, DOUGLAS ARTHUR

Complex Fill
 

Another solution and one that would survive sorting would be to
use the formula starting in A2 and then use the fill handle to copy down.
=ROW()-1

Double click on the fill handle, it will copy down as far as there is
data to the immediate left, or in the case of column A in the
immediate right. http://www.mvps.org/dmcritchie/excel/fillhand.htm

If I had read your thank you to Frank, I would have seen that your
were asking another question specifically addressing sorting, so
rather than another solution it is your new solution.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"yogendra joshi" wrote in message ...
Perfect hit Frank...

And Do not forget to Copy = Paste Special - Values so that the
formulas are no longer required.

Just an additional step so that the data integrity is maintained if
you sort or do some operation on the data !!!

Thanks,.

Yogendra

Frank Kabel wrote:
Hi
if you have these numbers in column A try the following:
- select column A
- hit F5 and choose 'Special'
- select 'Blank cells'
- now enter the equation sign '='
- hit the upper arrow key
- hit CTRL+ENTER

--
Regards
Frank Kabel
Frankfurt, Germany


Ken wrote:

Is there a single command that will fill each blank cell below a cell
with a number with that same number? There are 435 numbers and
different numbers of blank cells (including none) below each number.
A portion of the table is shown below. I want, e.g., the cell below

1

to show 1, the two cells below 2 to show 2, the two cells below 3 to
show 3 - and because there is no blank below four, move on to the

next

1, etc.

Ken

AR 1 BERRY, MARION
AR HUMPHREY, VERNON WOO
AR 2 GARNER, ED
AR PARKS, MARVIN
AR SNYDER, VICTOR F
AR 3 BOOZMAN, JOHN NICHOL
AR JUDY, JANICE (JAN) A
AR MORFEY, DALE WILLIAM
AR 4 ROSS, MICHAEL A
CA 1 MEYERS, WILLIAM P
CA THOMPSON, MIKE
CA WIESNER, LAWRENCE R
CA 2 HERGER, WALTER WILLI
CA VANCE, JEFFREY PAUL
CA 3 CASTILLO, GABRIEL E
CA LUNGREN, DANIEL E
CA OLLER, THOMAS R
CA OSE, MARY
CA TUMA, DOUGLAS ARTHUR