Excel formula to automatically increase the number of rows after X

Question

I imported several thousand rows of data into Excel, and while one element represented one row, I had to change each element so that 11 rows represented the same element identifier.

For instance: -

Original

63 --->data 64 --->data 65 --->data 

Current

 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 63 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 64 --->data 

(you get the idea) ...

However, because of the formula that I used to fill in the extra 10 rows per element, I remain with the same identifier in column A as all the rows on which the formula is based.

I am looking for a formula that will automatically increment cell values, but only every 11 rows, so that I can click and drag column A and it will fill in the same identifier for 11 rows and then automatically increase (+1) and fill in the next 11 rows .

I tried several options to no avail. Thanks.

EDIT

Here is an example of what I have and want to simplify: -

 ABCDEF 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 79 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 80 <--already correct id 58 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 57 <-- needs to be changes to 81 58 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 57 <-- needs to be changes to 82 

There are thousands of such lines ...

Answer 1

Here's a different approach if you're interested:

Enter 1 in A1

Then enter this formula in A2 :

=IF(MOD(ROWS($A$1:A1),11)=0,A1+1,A1)

Then just drag the formula from A2 down

Answer 2

You can also use this formula, it is also useful for even and odd numbering.

 =INT(((ROW(a1)-1)/11))*1+1 

use * 1 for 1 increment, * 2 for increment 2, +1 start number, if you want to start at 79 use +79 at the end

Answer 3

If you put a single column containing a straight sequence from 1 to the number of rows you have. (1, 2, 3, 4, 5, ...)

You can use this column to divide by 11, taking only the integer part of the result.

Suppose a straight-sequence column is A:

 = int(A1/11) = int(A2/11) 

Cm:

 AB Result 0 =int(A1/11) 0 1 =int(A2/11) 0 2 =int(A3/11) 0 3 =int(A4/11) 0 4 =int(A5/11) 0 5 =int(A6/11) 0 6 =int(A7/11) 0 7 =int(A8/11) 0 8 =int(A9/11) 0 9 =int(A10/11) 0 10 =int(A11/11) 0 11 =int(A12/11) 1 12 =int(A13/11) 1 13 =int(A14/11) 1 14 =int(A15/11) 1 15 =int(A16/11) 1 16 =int(A17/11) 1 17 =int(A18/11) 1 18 =int(A19/11) 1 19 =int(A20/11) 1 20 =int(A21/11) 1 21 =int(A22/11) 1 22 =int(A23/11) 2 23 =int(A24/11) 2 .......keep on until the last line 

Answer 4

If Im correctly understands the problem, there is no need for a complex formula.

try this in the column to check what you need.

Start with A1 and put the number 1 in each of the 3 cells (a1, a2, a3)

in A4, A4 = A1 + 1

then drag down. You will see the sequence that you need ... 1 1 1 2 2 2 3 3 3

if the sequence you need is really consistent, you can apply it as needed.