Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Rotating and Repeat series of Data in a Circle

Repeating text or values in a circle, from the beginning after the last of the completed data in Excel


In this 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' 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 the 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.

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