Search code examples
excelcopy-paste

How can I systematically duplicate the entries in a column into another column in Excel

In Excel I have a larger list of values in column A:

John
Jim
Joanne
Jenny

Is there a way in excel to copy paste those values into Column B, but creating N duplicates of each entry? For example with N=3 the result should look like:

John
John
John
Jim
Jim
Jim
Joanne
Joanne
Joanne
Jenny
Jenny
Jenny
Solution

  • Not literally copy and paste as far as I know but fairly easy with a spill formula in Excel 365:

    =INDEX(A:A,QUOTIENT(SEQUENCE(COUNTA(A:A)*3,1,0),3)+1)

    enter image description here

    In earlier versions of Excel you could do this:

    =INDEX(A:A,QUOTIENT(ROW()-1,3)+1)

    or (to avoid zeros on blank cells)

    =IF(ROW()>COUNTA(A:A)*3,"",INDEX(A:A,QUOTIENT(ROW()-1,3)+1))

    and pull it down.