Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between.
The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider A1:D100 range. (Note – There would be no blanks in between the values in the Grid)
Download the workbook related to this from Challenge 32 – Convert Matrix into Linear Column
The solution to this problem will be published after a month i.e. on 18-Jan-16.
If this comment is public, please hide it until solution is released.
Make $A$1:$D$7 a named range (I named it tblData)
In cell G1 use this formula and copy down:
=IFERROR(REPT(INDEX(tblData,INT((ROW(A1)-1)/COLUMNS(tblData))+1,MOD(ROW(A1)-1,COLUMNS(tblData))+1),1),"")