Excel - Duplicate Cells in a Column Individually-CodePudding

I have a column with unique numbers that I need to work. First, I'm needing to format the existing column so that each number is shown 13 times. I've attached a basic example of the principle. Is there an easier way to do this than just inserting rows and copying the number down? It's going to be across hundreds of numbers. Running O365

CodePudding user response：

You may try any one of the either,

For All Versions Of Excel,

=INDEX(A$1:A$5,INT((ROW()-1)/13) 1)

For Excel 2021 & O365 Users,

=INDEX(A$1:A$5,INT(SEQUENCE(13*A5,,0,1/13) 1))

Or, Using LET() Function

=LET(a,A1:A5,b,A5,INDEX(a,INT(SEQUENCE(13*b,,0,1/13) 1)))

Or, Use a LAMBDA() Function to create a custom, reusable function and refer them by a friendly name,

=DUPLICATEfx(A1:A5,A5)

Where,

DUPLICATEfx =LAMBDA(a,b,INDEX(a,INT(SEQUENCE(13*b,,0,1/13) 1)))

LAMBDA() Function used in Name Manager with a Defined Name as DUPLICATEfx with syntax as

=DUPLICATEfx(a,b)

Or, Use TOCOL() Function,

=TOCOL(INT(SEQUENCE($A$5*13,A1,0,1/13) 1))

Or, Use LET() with TOCOL()

=LET(a,A1,b,A5,TOCOL(INT(SEQUENCE(b*13,a,0,1/13) 1)))