How To Repeat A Range Of Items Multiple Times In Excel

In this tutorial I’ve created a formula that can retrieve a list of items, repeat that list, and automatically adjust if items are added to or deleted from that list.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
 

Here I have a list of cities on a tab called “Cities”:
71915-1

And on a tab called “Repeat” I’ve created a formula to copy that list of cities and keep repeating that list for as many rows as I copy the formula down:

071915-2

The formula is:

=IF(ISBLANK(INDIRECT(“Cities!A”&ROW(A2))),INDIRECT(“Repeat!A”&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2)

It used the following functions:

IF
ISBLANK
INDIRECT
ROW
ROWS
COUNTA

Here is a breakdown of the formula:

71915-3

In English, it is saying the following:

IF the cell referenced with the INDIRECT function (INDIRECT(“Cities!A”&ROW(A2)) ISBLANK, then run this formula: INDIRECT(“Repeat!A”&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))). If it is not blank, then just give me what’s in that cell (Cities!A2).

So, the formula will copy what is in the cells on the “Cities” tab until it hits a blank, then it will run the second INDIRECT formula which will go to the top of the list of the “Cities” tab and return that result. As the formula is continued to be copied down, it will then give the next cell, and the next cell and so on.

If I remove two cities from the list:

71915-4

My formula will automatically adjust the area where the formula is copied:

71915-5

Take a look at the video and see the details of how the formula works. This might come in very handy when copying dynamic lists!

 
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , , ,

3 Responses to How To Repeat A Range Of Items Multiple Times In Excel

  1. Dominic Munton January 26, 2017 at 6:36 am #

    Hi Michael,

    Thanks so much for sharing this tutorial. It’s got me most of the way but I’ve run into a hitch. My scenario is the same as yours apart from the list in “cities” is being dynamically generated by a formula. Unfortunately your formula is also repeating all the “” entries from my dynamic list. Is there a way to get your formula to ignore blank entries?
    Thanks!

    • Michael Rempel January 26, 2017 at 5:31 pm #

      Dominic, sorry I did not see your comment until today – don’t know why I didn’t get a notification. Anyhow, can you send me your file or a sample and I will gladly take a look at it. Send to mrempel@excel-bytes.com.

  2. Nemos January 29, 2017 at 5:09 pm #

    Great ! I tried for hours… and fortunately I found your solution!

Leave a Reply