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.
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
row_num = ODD number = 2n + 1
In Excel, you can use ROW() function instead n, so,
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:
In this, we use MOD() function to create our data, see below:
So, column_num completed formula: