Copy down (propagate) function/forumula needed

[Ernie Kohl]

I have a project I'm working on where I get a huge spreadsheet and I need to
"massage" the data to make it ready to load into a database. There are two
issues ~

The data I want to "copy down" is in the first column. For example cell
A1="some data", A2 A3 A4 are blank, A5="some data", A6 A7 are blank,
A8="other data", etc.

so... I want to be able to propagate whatever is in the first cell down
through the all cells below whether the cell is blank -or- the cell has the
same value (see cell A5). When a cell value changes, then the new text will
start propagating at that point and down. And so on.

A further wrinkle on copying this data is as follows... Let's say that cell
A15="data list 1" and then A16 A17 A18 are blank, A19="data list1" A20 A21
A22 A23 are blank, A24="data list #2" etc.

I want "data list 1" to end up being in cells A15-A23 (and then A24 will
have "data list #2" and so on). But when copying the contents of these cells
down, Excel copies the data as if it were a series of increasing numbers
(i.e., A19=data list1 A20=data list2 A21=data list3 ...)

Maybe this is easy to accomplish - I hope so. I've never been heavily into
Excel.

I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows XP.

 

[Ron Coderre]

Try this:

Select from the first data cell, down the column,
through the last cell you *want* to contain data.

Press [F5]......a shortcut for <edit><goto>
Click [Special]
Check: Blanks
Click: [OK]

Now...while all of the blank cells are selected
Type: =...to start building a formula
Press CTRL+UP arrow one time
Press CTRL+ENTER...to put that formula in each blank cell

Now that each cell has a value....if you want to hardcode them...
Select from the first data cell, down through the last in the column

From the Excel Main Menu:
<edit><copy>
<edit><paste special>....Check: Values....Click: [OK]

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ernie Kohl]

Ron,

thanks! that's perfect! it is going to save me soooo much time. I
appreciate it.

Ernie
--
Ernie Kohl

Try this:

Select from the first data cell, down the column,
through the last cell you *want* to contain data.

Press [F5]......a shortcut for <edit><goto>
Click [Special]
Check: Blanks
Click: [OK]

Now...while all of the blank cells are selected
Type: =...to start building a formula
Press CTRL+UP arrow one time
Press CTRL+ENTER...to put that formula in each blank cell

Now that each cell has a value....if you want to hardcode them...
Select from the first data cell, down through the last in the column

From the Excel Main Menu:
<edit><copy>
<edit><paste special>....Check: Values....Click: [OK]

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have a project I'm working on where I get a huge spreadsheet and I need
to
"massage" the data to make it ready to load into a database. There are
two
issues ~

The data I want to "copy down" is in the first column. For example cell
A1="some data", A2 A3 A4 are blank, A5="some data", A6 A7 are blank,
A8="other data", etc.

so... I want to be able to propagate whatever is in the first cell down
through the all cells below whether the cell is blank -or- the cell has
the
same value (see cell A5). When a cell value changes, then the new text
will
start propagating at that point and down. And so on.

A further wrinkle on copying this data is as follows... Let's say that
cell
A15="data list 1" and then A16 A17 A18 are blank, A19="data list1" A20 A21
A22 A23 are blank, A24="data list #2" etc.

I want "data list 1" to end up being in cells A15-A23 (and then A24 will
have "data list #2" and so on). But when copying the contents of these
cells
down, Excel copies the data as if it were a series of increasing numbers
(i.e., A19=data list1 A20=data list2 A21=data list3 ...)

Maybe this is easy to accomplish - I hope so. I've never been heavily
into
Excel.

I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
XP.

 

[Ron Coderre]

You're very welcome.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Ron,

thanks! that's perfect! it is going to save me soooo much time. I
appreciate it.

Ernie
--
Ernie Kohl

Try this:

Select from the first data cell, down the column,
through the last cell you *want* to contain data.

Press [F5]......a shortcut for <edit><goto>
Click [Special]
Check: Blanks
Click: [OK]

Now...while all of the blank cells are selected
Type: =...to start building a formula
Press CTRL+UP arrow one time
Press CTRL+ENTER...to put that formula in each blank cell

Now that each cell has a value....if you want to hardcode them...
Select from the first data cell, down through the last in the column

From the Excel Main Menu:
<edit><copy>
<edit><paste special>....Check: Values....Click: [OK]

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I have a project I'm working on where I get a huge spreadsheet and I
need
to
"massage" the data to make it ready to load into a database. There are
two
issues ~

The data I want to "copy down" is in the first column. For example
cell
A1="some data", A2 A3 A4 are blank, A5="some data", A6 A7 are blank,
A8="other data", etc.

so... I want to be able to propagate whatever is in the first cell down
through the all cells below whether the cell is blank -or- the cell has
the
same value (see cell A5). When a cell value changes, then the new text
will
start propagating at that point and down. And so on.

A further wrinkle on copying this data is as follows... Let's say that
cell
A15="data list 1" and then A16 A17 A18 are blank, A19="data list1" A20
A21
A22 A23 are blank, A24="data list #2" etc.

I want "data list 1" to end up being in cells A15-A23 (and then A24
will
have "data list #2" and so on). But when copying the contents of these
cells
down, Excel copies the data as if it were a series of increasing
numbers
(i.e., A19=data list1 A20=data list2 A21=data list3 ...)

Maybe this is easy to accomplish - I hope so. I've never been heavily
into
Excel.

I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
XP.

 

[kounoike]

You've already solved your problem. but this is another option, using macro.
first select the range you want to fill data, then run a macro below.

Sub fillblank()
Dim s As Range
On Error Resume Next
For Each s In Selection.SpecialCells(xlCellTypeBlanks).Areas
s = s.Offset(-1, 0).Resize(1, 1).Value
Next
End Sub

keiji