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.

enter image description here

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.


  • 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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.