Copy down (propagate) function/forumula needed

  • Thread starter Thread starter Ernie Kohl
  • Start date Start date
E

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.
 
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)
 
Ron,

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

Ernie
--
Ernie Kohl


Ron Coderre said:
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 said:
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.
Click to expand...
Click to expand...
 
You're very welcome.....I'm glad I could help.

Regards,

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

Ernie Kohl said:
Ron,

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

Ernie
--
Ernie Kohl


Ron Coderre said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top