How to repeat rows and add increment numbers at the end?

I need the formula to duplicate specific rows (the rows contain name data) a number of times and add incrementing numbers in the end.

It should look something like this:

I found a way to dublicale the rows, using this formula

``````=if(Rows(E\$2:E2) >\$B\$10*\$B\$11;""; index(A\$2:A\$7; mod(Rows(E\$2:E2)-1;\$B\$13)+1))
``````

Now I need to add numbers in the end. I am wondering if it is possible to do it in one formula, or do I need to create a separate column and concatenate it together.

• do you still seek the solution? – gamer0 Jun 27 at 2:45

1 Answer

Your issue can be solved using one Helper Column:

Since most of Excel commands are working with Google Spreadsheet, therefore this method can be applied on your Workbook also.

How it works:

• Fill Cell `L9`, `L10` & `L12` with `6`, `2`, `6`.
• Enter the Formula you have used to get name list in Cell `N2`.

``````=IF(ROWS(N\$2:N2) >\$L\$9*\$L\$10,"", INDEX(K\$2:K\$8, MOD(ROWS(N\$2:N2)-1,\$L\$12)+1))
``````
• Now, enter this Formula in Cell `O2` to get Helper Values (are simply Count of occurrence) and fill it down.

`=Countif(\$N\$2:\$N2,N2)`

• Copy Range `O2:O13`, using Paste Special pick Values & finish with Ok, on same Range `O2:O13`.
• Re-write the Formula in Cell `N2` with this one.

``````=IF(ROWS(N\$2:N2) >\$L\$9*\$L\$10,"", INDEX(K\$2:K\$8, MOD(ROWS(N\$2:N2)-1,\$L\$12)+1))&" "&O2
``````

N.B.

• Correction with Formula is `&" "&O2` at the end.

• Re-writing Range `O2:O13` is needed (using Copy & Paste as Value) to avoid the Circular reference for the new version of the Formula finished with `&" "&O2`.

• Helper Column is `Col O`

• You may adjust cell references as needed.