In this post, I’m going to walk you through how to take a list of items and repeat each list element X number of times to create a longer list.
Let’s say you have the following data:
John
Jane
Mary
And you want to repeat each name five times to produce:
John
John
John
John
John
Jane
Jane
… and so forth
Step 1: Create a column with 1,2,3,etc.
Step 2: Create another column that increments only after X occurences
In the first row of the new column, put the number 1
For this example, X occurences will be 5 to make the explanation easier to understand. Replace 5 with any other number and you’ll get the same results.
In the second row of the new column, you are going to use the following formula:
=if(MOD(A3,5)=1,B2+1,B2)
What this formula does is it uses the counter column we created in step 1 and only increments by 1 when it reaches 5 occurences.
The MOD formula returns the remainder of the counter column divided by 5. In the screenshot below, you’ll see that it increases from 0 to 4.
Now that we have a pattern, we utilize the IF function to increement the new row by 1 only when we have a MOD of 1. The two columns now look like this:
To finish, we need to refrence the original list using an Index function. The formula to use is:
=INDEX($E$1:$E$3,B2,1)
The INDEX function takes three arguments: The array, the row number, and the column. Copy the formula down
And that’s it! Commend below with any questions or if you know a better way to accomplish this task