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.
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...
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