Below is a possible solution to Challenge 32 – Convert Matrix into Linear Column – I
Enter following formula and drag down –
=IF(OFFSET($A$1,ROUNDUP(ROWS($1:1)/COLUMNS($A$1:$D$100),0)-1,
MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100)))=0,"",OFFSET($A$1,ROUNDUP(ROWS($1:1)
/COLUMNS($A$1:$D$100),0)-1,MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100))))
=INDEX($A$1:$D$7;1+INT((ROW(A1)-1)/COLUMNS($A$1:$D$7));MOD(ROW(A1)-1+COLUMNS($A$1:$D$7);COLUMNS($A$1:$D$7))+1)
The formula needs to take care of A1:D100 range. Hence, it has to be dragged down till 400 rows.
If you drag your formula down, you will notice that it will produce two 0s followed by #REF error.
Thank you for Thread MR Vijay A. Verma