Copy down (propagate) function/forumula needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ernie Kohl - 21 Mar 2008 20:20 GMT

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.

Signature

Ernie Kohl

Reply to this Message

Ron Coderre - 21 Mar 2008 20:29 GMT

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
[quoted text clipped - 30 lines]
> I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
> XP.

Reply to this Message

Ernie Kohl - 21 Mar 2008 21:10 GMT

Ron,

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

Ernie

Signature

Ernie Kohl

> Try this:
>
[quoted text clipped - 62 lines]
> > I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
> > XP.

Reply to this Message

Ron Coderre - 21 Mar 2008 21:12 GMT

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

Regards,

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

> Ron,
>
[quoted text clipped - 74 lines]
>> > I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
>> > XP.

Reply to this Message

kounoike - 23 Mar 2008 06:42 GMT

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

>I have a project I'm working on where I get a huge spreadsheet and I need
>to
[quoted text clipped - 30 lines]
> I appreciate any help I can get! I'm using Excel 2003 (SP2) on Windows
> XP.

Reply to this Message

Rate this thread: