Filling down a column and then increasing by 1 after a certain number of cells

[Boosted7]

Hello everyone,

I'm hoping someone might be able to assist with what is probably a fairly easy request:

I want to be able to enter a value into a cell (for example the number 1 into cell A1), and then I want this value to fill down 32 rows. Something like Fill (A1:A32 - if you get what I mean!). Then I would like the cell immediately under these 32 cells (cell A33, for example) to be 1 number higher than the preceding 32 rows, and then I want this new higher value to fill down 32 rows, then increase by one again and fill down for 32 rows etc etc.

The problem is I have a very large spreadsheet with 10s of thousands of rows, so I would like this to be able to fill down in one column to the bottom of my spreadsheet.

Could anyone please help me as I'm feeling rather stuck at the moment!

Thanks!

[Harribone]

Using your example situation with 1 entered into cell A1 type into A2:

=IF(COUNTIF($A$1:A1,A1)=32,A1+1,A1)

You can then drag this down to do the job, just need to tweek this fomula to work in your real life situation.

EDIT: Should be complatible with 2003 but test this on a mock file first to save messing up your file and let me know should any of the functions not work (COUNTIF for example is a function)

[oeldere]

a1 is your value e.g. 1

a2=if(roundup(Row()/32,0)>$A1,$A1+1,$A1)

and drag down

[Boosted7]

Thank you both!

Oeldere, I just tried your formula and it works nicely! If the value in A1 is something other than 1, do I need to change the formula aat all?

[oeldere]

I suggest you will test your question, and you will see the result.

[Boosted7]

Indeed! Apologies for such a noob question.

Thanks again

[oeldere]

Hi boosted7

now i'm interested in the result of your test.

[newdoverman]

If you substitute cell references for the 32 and the 1 after the + sign, you can easily customize the formula to give different series.

This is what I mean: a2=if(roundup(Row()/N1,0)>$A1,$A1+N2,$A1)

The N1 and N2 are what I was referring to. Those references can be anything convenient.

Create a series and then change the values. You will see the whole series change instantly to match your criteria.

Nice formula oeldere

[oeldere]

@newdoverman,

Thanks for the compliments, realy appreciate it.

Thanks for contributing with the cell references.

[newdoverman]

You are welcome! The idea "just came to me". Maybe it will have a useful application for someone.

[Boosted7]

Thank you guys for your help.

Now I just have one other thing I'd like to be able to do:

My problem now is that some of my spreadsheets are big with several 10s of thousands of rows in 5 columns. It takes ages to click and drag the cell manually to the bottom of each spreadsheet. Is there something that can be added to this formula so that it will automatically "fill" down the column so that it goes down as far as the last value on the adjacent column? This would save me loads of time!!

Thanks again

[oeldere]

Yes there is; only if there is no gaps in the data.

Excel 2007 => insert table

After making a table, you insert the formula in the first cell of the column (with headers) and it will automatic expand to the end of the table.


Not sure if the table is also available in Excel 2003.

Otherwise you have to use VBA to achieve your goal.

[Boosted7]

Thanks again oeldere. I'm actually using Excel 2010 now, oops I must update my profile!

I'll give your suggestion a try!

[newdoverman]

If you know what the last row is you can very quickly copy down to that row.

Click in the cell that has what you want to copy down the column. You will notice to the left of the formula bar there is a NAME bar that has the address of the active cell. Click twice in that bar so that the cursor is blinking beside the active cell reference. Enter a colon and the last cell in the range so that you have a cell reference in the bar like A1:A10000. Now hit enter. That entire range will be selected with the active cell still active. Now, Ctrl+D to copy the active down the length of the range.