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

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.