excelexcel-formula

Excel - Repeat list items multiple times

I've got a list of text in excel like the input below, I would like to repeat each of the list items 3 times, in order, using a formula to achieve the Output below.

I've explored trying to do this using REPT() but couldn't get it to work. Any ideas ?

I've made a test google sheet to try it in here : https://docs.google.com/spreadsheets/d/17E-0Vr3fgJQw-A5HfUdH4jINl00NClbToWa32mqotbY/edit#gid=0

INPUT

existing list

OUTPUT

output list

Solution

  • In Office 365:

    =INDEX(A:A,INT(SEQUENCE(COUNTA(A:A)*3,,1,1/3)))

    NOTE: In testing, some sequences will cause this to error due to Floating decimal errors. Please see the answer by Joel on how to deal with this if you are getting the wrong sequences.

    enter image description here

    In older versions put this in B1:

    =INDEX(A:A,INT((ROW($ZZ1)-1)/3)+1)&""

    And copy down till you get blanks.

    enter image description here