2

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.

  • 1
    Try =(((COLUMN()-1)*100)+ROW())+(FLOOR(ROW()-1,100)*15) – Andi Mohr Mar 5 '17 at 21:04
  • Hi Andi, the formula works great, thanks again, but tell me something. How can i insert some text in the first row without influencing the calculation? If i insert something in the first row the formula is sticking to ne row number..? Thanks again. Nikola – Nikola Ristic Mar 6 '17 at 16:52
  • You need to tweak the ROW() offsets. I've added a full answer for you below. Hope it makes sense! – Andi Mohr Mar 6 '17 at 17:12
0

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.

| improve this answer | |
  • The key to this question is the fact you get to row 101 and the numbers jump by 1600 - the formula I posted quickly, addresses this and you can just copy and paste down as far as you like once, to generate thousands of entries. There's probably a more elegant formula than the one I bashed together quickly though. – Andi Mohr Mar 5 '17 at 23:08
0

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.

| improve this answer | |
0

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 image description here

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

enter image description here

| improve this answer | |
  • The key to this question is the fact you get to row 101 and the numbers jump by 1600. – Andi Mohr Mar 6 '17 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.