Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
complex custom fill series with formula | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) |