Help with formula that will copy when blank but reset/skip when not.

[LightingPop]

Hi!

I have a sheet that looks like this: http://i.imgur.com/rQ0kq.png

And I need it to looks like this: http://i.imgur.com/Vg0BN.png

It is a very big sheet and basically there is no way to manually fill in the blank cells with the proceeding value. I need to be able to copy down the number (Cell: A1) into the blank cells (Cell: A2:A4) below it and then when the new number appears (Cell: A5) it needs to not override it but skip it and then copy it down until the next number appears.

I feel like this is hard to explain in words but the pictures above do a better job. Any help at all would be greatly appreciated!

[Moo the Dog]

Actually, the pictures above are identical.

[LightingPop]

I just caught that and edited it sorry.

[Pete_UK]

Your second picture is the same as the first, but I think I know what you want to do, and here's a bit of Excel magic:

First, select all the cells from A1 down to where you want to fill in the blanks.
Then press F5, and click on Special, and then against Blanks, then OK.
Then begin to enter a formula by typing =
Then click on the cell above the active cell (i.e. A1)
Then say the magic word ...

and hold down the CTRL key and press <Enter>

Abracadabra - all the blanks cells are filled with the value from the cell above.

Magic !!

Hope this helps.

Pete

[LightingPop]

Thanks for the help but it doesn't seem to work. Every time I try it only recognizes one bank of blanks cells and not all the blank cells in the column.

[Pete_UK]

You need to highlight all the cells in that column that you want to affect when you start.

Here's another approach to the beginning:

Select cell A1
Press F5, then click on Special, then click on Current Region, then OK
Press F5 again, click on Special, then blanks, then OK

and carry on as above.

Hope this helps.

Pete

[LightingPop]

Thanks. This is so frustrating. When go to special select blanks it only highlights about 20% of the blanks the first time and it doesn't copy the cell anyways. Then when I try again Excel says there are no blanks yet about 80% of the column has blanks.

[Pete_UK]

Maybe those blanks were not really blanks - perhaps they contained spaces or other non-printable characters.

Pete

[LightingPop]

I did a copy and paste special values only and it still saw blanks were there wasnt.

[Pete_UK]

Select cell A1, press the <end> key once, followed by the <down-arrow> key. If the cursor goes right down to the bottom of your list then there are no blank cells in the list, even if some of them look empty. In a column B cell against a cell which looks empty you can put this formula:

=LEN(A_cell)

which will tell you how many characters there are in that cell (should be zero for an empty cell).

Hope this helps.

Pete

[LightingPop]

Yeah... there is some kind of unprintable values in all the cells. I thought if I did a copy to past special values only it would only copy the visible values. Is there another way?

EDIT:

When I click on the "blank" cells there is nothing in them at all not even an extra place.

[Pete_UK]

Instead of putting that =LEN(A_cell) formula, put this one in a few places:

=CODE(A_cell)

this will give you the ASCII code of (the first one of) those characters. I suspect this will give you 160, which is the non-breaking space character, and which often finds its way into sheets if you have copied some data from a website.

You can get rid of this in one operation using Find & Replace. Highlight column A, then CTRL-H, then in the Find What box you should hold down the Alt key and type 0160 on the numeric keypad. Leave the Replace box blank, then click Replace All.

If the little formula returns 32 that is the code for a normal space, and you can use Find & Replace to get rid of that too in the same way.

Hope this helps.

Pete

[LightingPop]

Ugh this is crazy, whenever I try the =CODE(A_cell) it bounces back with a #NAME?

[Pete_UK]

Yes, but you have to put in the actual row reference. For example, if you are in B13, then the formula is:

=CODE(A13)

Then you can copy that down into other cells in column B where column A cells look empty.

Pete

[LightingPop]

Ok, I might be getting somewhere. It looks like this with the formula in:

http://i.imgur.com/Ffm7N.png

[Pete_UK]

Rather than post images of the screen, why don't you attach your workbook? Just get rid of any sensitive data that you might have first.

Pete

[LightingPop]

I attached it. The problem is I have about 12 other books that I need to do the same thing with so I kinda have to know how to do it.

[Pete_UK]

Okay, there is something strange about this data that I don't quite understand. Using LEN, those cells that appear empty return a zero length, although <end> followed by <down-arrow> takes you to the bottom of the list, which implies that there is something in those cells. Anyway, here's what you should do with your copy of this file (and with the others):

Highlight column A by clicking on the column identifier.
Click on Data | Text-to-Columns
Click Finish on the first panel.

That seems to get rid of the anomalies, so you can then do the "magic" trick. I've done this in the attached file for you, so you can see what you end up with. If you want to sort this data afterwards, you must fix the values first.

Hope this helps.

Pete