I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help. Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20

Assume your data is in col A, starting in row 2
Insert a blank column to the left, so that the data is now in column B
In A2 enter the formula

=if(b2<B1,B2,b1)

Copy it down to the last desired row
Select all these new formulas and copy them
Use Edit-Paste Speical-Values to replace formulas with the results.
Delete the original column of data.

"Jannie worksheet function" wrote:

I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help. Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20

Select that column, use Edit/Goto Special, click Blanks and OK. Then,
whatever your selection is, type =, then press ctrl/enter & you're done.
Bob Umlas
Excel MVP

"Jannie worksheet function" <Jannie worksheet
wrote in message
...
I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would
make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help.
Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20

Select column A then F5SpecialBlanksOK

Enter an = sign in active blank cell then point or arrow to cell above.

Hit CTRL + ENTER.

Copy column A and in place Paste SpecialValuesOKEsc.


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 10:52:00 -0700, Jannie worksheet function <Jannie worksheet
wrote:

I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help. Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20

Duke,
Not working quite right. Column 1, see the missing numbers this col. should
look like the one next to it. The formula should be for col. A not B. Also,
it is not putting in the correct # it is putting in zeros.
Jannie

"Duke Carey" wrote:

Assume your data is in col A, starting in row 2
Insert a blank column to the left, so that the data is now in column B
In A2 enter the formula

=if(b2<B1,B2,b1)

Copy it down to the last desired row
Select all these new formulas and copy them
Use Edit-Paste Speical-Values to replace formulas with the results.
Delete the original column of data.

"Jannie worksheet function" wrote:

I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help. Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20

Got to work thanks so much.
Jannie

"Gord Dibben" wrote:

Select column A then F5SpecialBlanksOK

Enter an = sign in active blank cell then point or arrow to cell above.

Hit CTRL + ENTER.

Copy column A and in place Paste SpecialValuesOKEsc.


Gord Dibben MS Excel MVP

On Wed, 22 Aug 2007 10:52:00 -0700, Jannie worksheet function <Jannie worksheet
wrote:

I have a worksheet with thousands of rows of data. One column has data as
shown below. Notice the blank lines. What I need is a formula that would make
the data look like the column I show next to it. I know iI have seen this
written before but I can't remember the formula to do it. Please Help. Thx.
Jannie
10 10
10
10
10
12 12
52 52
60 60
60
60
40 40
40
20 20