9

I imported a few thousand rows of data into Excel and whereas one item represented one row, I've had to modify each item so that 11 rows represent the same item id.

For example:-

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, due to the formula I have used to populate the additional 10 rows per item, I am left with the same ID in Column A as all the rows the formula was based on.

I'm looking for a formula that will auto-increment the cell values based but only every 11 rows, so that I can click and drag down column A and it will fill the same id for 11 rows and then auto-increment (+1) and fill the next 11 rows like this.

I've tried a number of variants all to no avail. Thanks.

EDIT

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

A    B    C    D    E    F
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 rows like this...

Community
  • 1
  • 1
zigojacko
  • 1,983
  • 9
  • 45
  • 77
  • what do you mean formula for auto-increment. as far as i know a formula is a math function (or vba function) that takes as input a range of cells and writes 1 value to the destination. – Lefteris E Mar 26 '13 at 15:00
  • If you write '3' in a cell and then click and drag the cell down it auto-increments +1 in each cell. I'm after a formula that does this but only every 11 rows. Something along the lines of =SUM(cell-11rows+1). – zigojacko Mar 26 '13 at 15:04

4 Answers4

17

Here's another approach if you're interested:

Enter 1 into A1

Then enter this formula into A2:

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

Then just drag the formula from A2 down

Joseph
  • 5,070
  • 1
  • 25
  • 26
  • Saved my time, a lot of it! Thanks!! – DagicCross May 02 '16 at 15:55
  • 2
    this formula is working great! thank you! (for any number just change the "11" by any other you need. I needed that for OpenOffice tho, here are the changes incase it helps someone later `=IF(MOD(ROWS($A$1:A1);11)=0;A1+1;A1)` – user3916429 Oct 05 '16 at 14:59
7

You can also use this formula, it will also usefull for even and odd numbering

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

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

Ibrahim Akbar
  • 119
  • 1
  • 8
6

If you put one column containing a straight sequence from 1 to the number of lines you've got. (1, 2, 3, 4, 5, ...)

You can use that column to make a division by 11, taking only the integer part of the result.

Supposing the column with straight sequence is A:

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

See:

A        B            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
Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • I inserted another column (A) and added 1,2,3,4,5,6,7,8,9,10,11 in columns A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12 (row 1 has headers) - as in 1 in A2, 2 in A3 (etc). I don't even know if this is what you meant but I'm unsure what to do next. – zigojacko Mar 26 '13 at 15:41
  • I've included a sample of what I am trying to achieve in the original question. Thanks. – zigojacko Mar 26 '13 at 15:46
  • That's it. Now in another column use the formula `= int(A2/11)`. – Daniel Möller Mar 26 '13 at 15:46
  • Do that for the entire column (A2, A3, A4 and so on...). You will see that new column with the formula will bring eleven 0 values, eleven 1 values, eleven 2 values.... – Daniel Möller Mar 26 '13 at 15:47
  • 1
    Do that in the entire column, don't stop at 11, go forth, 12, 13, 14, 15, 16, 17....until the last line. – Daniel Möller Mar 26 '13 at 15:49
  • Just edited to show what I mean. Use that result in your formulas to achieve your goal. – Daniel Möller Mar 26 '13 at 15:55
1

If Im understanding the issue correctly there is no need for a complex formula.

try this in a column to test for your self to see if this is what you need.

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

in A4 put A4 = A1+1

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

if the sequence you need is indeed sequential then you can apply this as needed.

Adam Daniel
  • 13
  • 4