How to fill 50.000 numbers, in blocks of 1500 in 16 columns?

Question

So here is the issue. If A1 to A100 are from 1 to 100 then B1 is 101 and B100 is 200 etc.. Then P1 is 1501 and P100 is 1600 then i need A101 to be 1601 and continue for the next 1500 numbers then again after P last need to continue to A.. so like blocks of 1500 numbers. How to create a table of 500K numbers like this?

So far no luck except manually.

Answer 1

Excel is pretty smart when predicting.

In A1 enter 1, then A2 enter 2... through A3 or A4 or so.

Then, Highlight or select A1 through A4, then click the bottom right of the cell last selected (A4) where you see a small x. Drag this down to A100 and the numbers will self populate.

Now, on B1 enter =A100+1, B2 enter B1+1, Then copy B2 and select B2 through B100 and all the numbers will fill in.

Then Copy column B, and paste columb B into colums C through P.

Finally if you won't want those formulae, copy all data, then Paste Special, Values only.

Answer 2

If you want your numbers to start in cell A1 without a header row, use this formula:

=(((COLUMN()-1)*100)+ROW())+(FLOOR(ROW()-1,100)*15)

If you do want a header row, so your numbers start in A2, adjust it like this:

=(((COLUMN()-1)*100)+ROW()-1)+(FLOOR(ROW()-2,100)*15)

How this works:

This formula uses the 'grid reference' provided by row and column numbers, generated by ROW() and COLUMN() functions.

The formula comes in two parts:

1. The first bit (((COLUMN()-1)*100)+ROW()) takes the current row number, and adds 100 to it for each column to the right (so column B is column number 2, minus 1*100)

2. The second bit '(FLOOR(ROW()-1,100)*15)' adds 1500 every time you go down another block of 100 - ROW() tells Excel that the current cell is greater than row 100 so it needs to add 1500, and the FLOOR() function rounds that row number down to the nearest 100 so Excel knows how many lots of 1500 to add.

Answer 3

There are two issues here:

Select the desired range:

In Excel you can also make a selection by typing it's address in address bar:

Enter the numbers:

• Type formula:
  =ROW()+(COLUMN()-1)*100
• press CTRL+ENTER - this will enter your formula to all selected cells
• if you want to get rid of the formula, and keep only the numbers, then press CTRL+C, then go to home - paste - special - values