Copy data to blank rows (2007)

Topic

New Reply

Hi all:

I’ve seen this before here some time ago, but for the life of me I can’t find it through search. I have several thousand rows of the type:

NAME DEPT AREA OPTION
Fred // Dept A // Cardiff // Choice 1
Blank cells – – – – – – – – – – Choice 2
Blank cells – – – – – – – – – – Choice 3
Jim // Dept B // Swansea // Choice 1
Blank cells – – – – – – – – – – Choice 2
Blank cells – – – – – – – – – – Choice 3
Blank cells – – – – – – – – – – Choice 4
Mary // Dept B // Cardiff // Choice 1
Blank cells – – – – – – – – – – Choice 2

What is the trick to quickly convert it to the form:

NAME DEPT AREA OPTION
Fred // Dept A // Cardiff // Choice 1
Fred // Dept A // Cardiff // Choice 2
Fred // Dept A // Cardiff // Choice 3
Jim // Dept B // Swansea // Choice 1
Jim // Dept B // Swansea // Choice 2
Jim // Dept B // Swansea // Choice 3
Jim // Dept B // Swansea // Choice 4
Mary // Dept B // Cardiff // Choice 1
Mary // Dept B // Cardiff // Choice 2

I can’t remember whether the technique involved VBA or was done with formulae?

Many thanks for any help.

Tony

Reply | Quote

Replies

Select the data range:
Edit – Goto – [Special]
Select “Blanks”
[OK]

Now the blank cells will be selected. In the active cell, enter an equal sign(=) and then select the cell above it, putting in a formula. Instead of [Enter] to confirm it, use Ctrl-Enter and this will put the formula in all the cells, filling all the blanks with a reference to the cell above it.

Select all the cells
edit – copy
edit – pastespecial – values
to convert the formulas to values…

Steve

Reply | Quote

Select the data range:
Edit – Goto – [Special]
Select “Blanks”
[OK]

Now the blank cells will be selected. In the active cell, enter an equal sign(=) and then select the cell above it, putting in a formula. Instead of [Enter] to confirm it, use Ctrl-Enter and this will put the formula in all the cells, filling all the blanks with a reference to the cell above it.

Select all the cells
edit – copy
edit – pastespecial – values
to convert the formulas to values…

Steve

Thanks Steve – this is just what I remembered, but couldn’t find!

Of course, being 2007, the Goto command is in the Find & Select group of the Editing Tab – but I used F5 anyway.

Many thanks for your prompt help.

Reply | Quote