repeat range multiple times

[Aleksandar]

I have a problem. In column B is list of 5 products. Is there a way to copy each product and paste 5 times in column D? I will need to know this in future with larger data sets. In file I copied each product and pasted manually but I need this to be automated in future.

 

[bosco_yip]

In D4, copied down :

=IFERROR(INDEX(B$4:B$8,INT((ROWS($1:1)-1)/5)+1),"")

Regards
Bosco

 

[GraH - Guido]

Possible alternative with Power Query

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  MakeList = Table.AddColumn(Source, "RepeatValues", each List.Repeat({[Product]},5)),
  RemoveFstCol = Table.RemoveColumns(MakeList,{"Product"}),
  ExpandListAsRows = Table.ExpandListColumn(RemoveFstCol, "RepeatValues")
in
  ExpandListAsRows

 

[Aleksandar]

It works great. You saved me a lot of work. Thanks.