Repeat a formula 5 times before changing cell reference

Hello,

Does anyone know how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?

Hi,

Could you please upload your workbook? I could suggest a formula that would do the trick without it, but it's much easier to provide you with a solution that you don't have to then fit into your already existing workbook.

Thanks

Attached is the file. On the TE Details Tab starting in A4 is where I need it to repeat 5 rows. ANO through XCL in column B. Then you will see ANO through XCL repeat over and over down column B. The codes I need populated in column A are from the running list tab column W but I want the formula to skip any value in column W on the running list tab that is labeled Duplicate or Added so that I just get the rates that I need loaded into our software.

The products are listed on another tab though and in the list of products there are duplicates and added listed in the same list. I need to exclude those. What this is doing is getting excel data into a loadable SQL format so that they can load info into software converting excel calculations into system calculations.

I can't remove duplicates on the first tab. 100% of the data needs to remain on that tab.

Here is the file again where I added a formula on Sheet1 that removes duplicates using a formula. This is the list I would need to populate in the request above.

modifying the formula above to look at sheet one for the list without duplicates worked. Thanks so much for your help. Awesome.

=IFERROR(INDEX(Sheet1!$O$4:$O$80,CEILING(ROWS(C$2:C2)/5,1)),"")