Excel Help

[Rhino172]

I know I have worked this out in the past and I'm convinced it was with an IF formula but hoping someone here can help...

A1 = 1
A2 through A5 are blank
A6 = 2
A7 through A10 are blank
A11 = 3
A12 through A20 are blank
The sheet continues like this, sometimes there is one row that is blank, sometimes there are 5 or 6 blank rows.

How can I fill the blanks with the number above, so basically I want A2 through A5 to = A1 and A7 through A10 to = A6 etc etc

I'm sure in the past I inserted a column and used an if formula. Obviously I can copy paste etc but the list has over 27k rows of data so thats gonna take an age!

hopefully this makes some sense lol!

 

[Sym0n]

hopefully this makes some sense lol!

I've no frickin' clue what you're after or trying to do lol.

 

[Rhino172]

pmsl it's not very easy to explain tbh and it's annoying the hell out of me as I know I've done it in the past!

 

[Rhino172]

Before: how it is currently

After: how I want it

Look at column A, I need a formula that I can just paste in/fill down (not pasting into each cell as that would be no different from just copy/paster and take forever) that will populate the blank cells. Hopefully that will make it a bit clearer...

 

[Adam.]

Underneath 1 type one again. Highlight both cells then drag down by the corner. (small black square)

You need the 2 numbers the same otherwise it will start 1 2 3 4 5 etc.

 

[Rhino172]

yes but that will then overwrite the 2 and 3 etc and put 1 all the way down the whole column.

remember the issue here is the sheet is HUGE, I want something I can just fill down for the WHOLE sheet not just a "section" at a time.

It can be done as I've done it in the past but can't remember what I did lol

 

[The Boosh!]

easiest way to do that would be to insert a row above your data

put an auto filter on said row.

filter column a by blanks

type in blank cell = & cell reference above (so in your case you would do =a2 once you've added in the new row)

copy this cell, select all blank cells and paste. then C&P special values.

Alternatively you can automate it. if statement is possible but i'd use a doloop bit of vba and attach it to a button.

 

[Rhino172]

I'm not following you there....far too complicated compared to what I did in the past.

 

[Adamio]

Thought i had it but it doden't work, sorry

 

[The Boosh!]

LOL it's not complicated, i've just done it with 66,000 rows of mock data based on your screen shots above.

 

[Sym0n]

Right now I understand. Try this:

Select column A
Press Ctrl+G
Click Special
Put a dot in the "Blanks" radio button
Click OK
Type =
Press the up arrow
Hold CTRL down and hit return

If that doesn't work then there's some VBa you could use.

 

[Blighty]

Will the adjacent letters always be certain ones? I.e. can you have an IF statement to fill the number by the letter next to it?

 

[The Boosh!]

Type this in cell i2, then drag down:

=IF(ISNUMBER(A2),A2,I1)

There is no easy formula way to do it within the same column, unless you use visual basic.

 

[The Boosh!]

what i'm on about below!

 

[Rhino172]

Type this in cell i2, then drag down:

=IF(ISNUMBER(A2),A2,I1)

There is no easy formula way to do it within the same column, unless you use visual basic.

still got no idea on this one lol, I think my brain has finally stopped....

Right now I understand. Try this:

Select column A
Press Ctrl+G
Click Special
Put a dot in the "Blanks" radio button
Click OK
Type =
Press the up arrow
Hold CTRL down and hit return

If that doesn't work then there's some VBa you could use.

Perfect! defo not what I did before but it does the job! ta