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

Question

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.

Answer 1

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.