CODEWITHSUNDEEP
excelcopy-paste
Michael S
Michael S

Reputation: 880

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

Upvotes: 0

Views: 42

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

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.

Upvotes: 0

sascha4532
sascha4532

Reputation: 114

Sub NameCopy()
    Dim r As Long
    Dim i As Integer
    Dim n As Integer
    Dim c As Range
    Dim lastrow As Long
    r = 1
    n = 3
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In Range("A1:A" & lastrow)
        For i = 1 To n
            Cells(r, 2).Value = c.Value
            r = r + 1
        Next i
    Next c
End Sub

Upvotes: 1

Related Questions