Sometimes we deal with data that is not standardized that we might give up, you should learn how to rebuild you data, OK, let's start the case.

## Source Data

## Rebuild Data

## INDEX formula

You might think use **INDEX()** function, right! but how?

The **INDEX()** function syntax:

`INDEX(array, row_num, [column_num])`

After analysis, you may find:

`row_num = 1;3;5`

`column_num =1,2,3,4,5,6,7`

## row_num

`row_num = ODD number = 2`

** n** + 1

In Excel, you can use

**ROW()**function instead

**, so,**

*n*`row_num = 2*`**ROW()** + 1

But, we need **row_num** repeat 7 times, OK, `INT(ROW()/7)`

, just repeat 7 times, let's see below picture to show the process:

So, **row_num** completed formula: `=2*INT((ROW()-1)/7)+1`

## column_num

In this, we use **MOD()** function to create our data, see below:

So, **column_num** completed formula: `=MOD(ROW()-1,7)+1`

## Completed formula

I1 `=INDEX($A$1:$G$6,2*INT((ROW()-1)/7)+1,MOD(ROW()-1,7)+1)`

J1 `=INDEX($A$1:$G$6,2*(INT((ROW()-1)/7)+1),MOD(ROW()-1,7)+1)`

stephan says

I used two columns as counters, one for row# and one for column#. row# starts at =2 and increases by 2 periodically when next col# would be >7. Column number increases by 1 every row, until next col#>7 in which case it resets to 1.