Blue Flower

Repeating Text or Values in a Circle, from the Beginning after the Last of the Completed Data

In this Excel tutorial I will show how we defined series data that is either text or numerical value, rotate in a circle, ie to repeat series data after the last of the original data. Also, I will show how we can fill a certain range of cells, data contained in series, so that after the last data continue series data with the first data ie repeated series again.

In the picture below you see our desired result. So in the column 'M' you note the original data. Our goal is to fill a range of cells "A1:I5' with the original series data so that after the last of the completed data from a range of data, we continue to fill with the first data of the respective series data.

Filling in range of cells series data with the repeat series

Repeating Series Data In A Circle

 

This problem we can solve by making the extra column to create a repetition of the original series data (see figure below).

Repeat series data after the last completed data

So in the column 'O' let's create and copy the following formula is determined by the number of rows down.

ARRAY formula in cell 'O2' as follows

=IF(ROWS(M$2:M2)>10*COUNTIF($M$2:$M$33;"="&"?*");"";INDEX($M$2:$M$33;SMALL(IF($M$2:$M$33<>"";ROW($M$2:$M$33)-ROW($M$2)+1);MOD(ROWS(M$2:M2)-1;COUNTIF($M$2:$M$33;"="&"?*"))+1)))

Note the number 10 in formula. This number determines the number of times to repeat the series data from column 'M', in the auxiliary column 'O'.

After creating a repeating series data in the destination cell ranges enter the following formula.

The formula in cell 'A1' (copy right)

=INDIRECT("O"&(4*(COLUMN(A1)-1)+ROW(A1))+1)

The formula in cell 'A2' (copy right)

=INDIRECT("O"&(4*(COLUMN(A1)-1)+ROW(A2))+1)

The formula in cell 'A3' (copy right)

=INDIRECT("O"&(4*(COLUMN(A1)-1)+ROW(A3))+1)

The formula in cell 'A4' (copy right)

=INDIRECT("O"&(4*(COLUMN(A1)-1)+ROW(A4))+1)

Now you notice the results of the formulas (see picture above). Our goal was to zigzag fill the destination range with a series of original data.

Circular rotation of the text from the column to the circle one after the other if the condition is met

This case is based on the same principle as the previous case, with small differences. Notice the situation in the picture below under 'A' column there is a text list of radio shows. All data is unique and these data are entered manually, so there is no formula in the column. In the 'B' column is the duration of the radio emission from the same row. The 'C' column contains a formula that shows the circular repeat of a series of radio broadcasts from the 'A' column. This list serves as a column of auxiliary data from which, we will return results in the 'F' column.

The 'E' column shows the start time of each radio show. The 'F2' cell using drop-down menu choose which will be radio broadcast as the first in a series. In the 'F2' cell, furthermore, there is a formula that returns the sequence of the line in relation to the condition of the 'F2' cell. So, the circular sequence of all radio shows is shown. The 'G' column automatically shows the duration of each radio show relative to the adjacent cell from the 'F' column.

Rotate a string of text data in a circular schedule

Return sequence data of the series from the A column based on the condition of 'F2' cells

For the solution of this task, the following formulas are used in the picture above.

The formula in 'A101' follows the cell below. (This formula counts how many cells are filled in the 'A' column. Notice the formula expression. I have set this formula because of the options, if there is a list of all the radio shows in the 'A' column that came up with a formula. Some other formulas will not return the correct result of counting the filled cells if there is a formula in the 'A' column but showing the empty cell. This formula will count correctly if there is a formula in 'A' column).
=COUNTIF($A$2:$A$100;"="&"?*")

ARRAY formula (CSE formula) in 'C2' cell follows the below. (This formula return a circular sequence or list in a circle based on cells that contain text in the 'A' column. This formula, up to as many rows as you believe you can be circular repetition. eg we have 11 radio shows, 11*9=99 rows, so i have predicted 9 times repeating a unique text or circular data sequence from 'A' column. Of course, you can hide this column or move it to another location.).
=IF(ROWS($A$2:$A$100)>$A$101*COUNTIF($A$2:$A$100;"="&"?*");"";INDEX($A$2:$A$100;SMALL(IF($A$2:$A$100<>"";ROW($A$2:$A$100)-ROW($A$2)+1);MOD(ROWS(A$2:A3)-1;COUNTIF($A$2:$A$100;"="&"?*"))+1)))

In 'E2', enter the radio start time manually, ie start the first radio show.

The formula in 'E3' cell follows the below. (This formula is copied to the last order you want. This formula return the start time of the next radio show based on the time of the previous radio broadcast).
=IFERROR(IF(F3="";"";E2+G2);"")

In 'F2' cell set the 'drop-down menu' that you can change, which radio show will start the first.)
The drop-down menu (Data Validation List) without empty cells uses the following formula. So set this formula to the "Source" field.
=OFFSET(A$2;;;(COUNTA($A$2:$A$100)+COUNTBLANK($A$2:$A$100))-COUNTIF(A$2:A$100;"");)

The formula in cell 'F3' follows below. (This formula continues with a list of all radio show based on the first radio show from the 'F2' cell and continues to circulate)
=IF(ROW()-1>$A$101;"";IF(OFFSET(C1;MATCH($F$2;$C$1:$C$100;0)+ROW($F$1)-1;0)=0;"";OFFSET(C1;MATCH($F$2;$C$1:$C$100;0)+ROW($F$1)-1;0)))

Formula in 'G2' cell follows below. (This formula returns the duration of the B column based on the condition in the F column. Copy a formula below).
=IFERROR(IF(VLOOKUP(F2;$A$2:$B$100;2;FALSE)=0;"";VLOOKUP(F2;$A$2:$B$100;2;FALSE));"")

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula.
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.