Continuous column array from a non-complete 2d matrix array

[learjsy]

I have a 2d-matrix array but not all cells contain data. I need a formula to map all of the non-blank data to a continuous column (by rows of the matrix).

I found the following online, which works perfectly except the output includes the blanks from the source matrix: OFFSET(Matrix,TRUNC((ROW()-ROW($BF$5))/COLUMNS(Matrix)),MOD(ROW()-ROW($BF$5),COLUMNS(Matrix)),1,1).

This website appeared to offer a solution to removing the blanks, but I couldn't get it to work with my output above. https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range

Ideally I'd like to do the mapping in one go, but I guess a second step (converting to a column, then removing blanks) would work too.

Thanks in advance

 

[learjsy]

For future reference / anyone looking for an answer with a similar issue:

I solved this eventually using a combination of a two step-solution (similar to the above) and an elimination sequence to determine what was going wrong.

The first step - using OFFSET(Matrix,TRUNC((ROW()-ROW($BF$5))/COLUMNS(Matrix)),MOD(ROW()-ROW($BF$5),COLUMNS(Matrix)),1,1) - worked perfectly for mapping the 2d (non-complete) array into a single column. The issue was the second step above - IFERROR(INDEX($BF$5:$BF$50,SMALL(IF(ISTEXT($BF$5:$BF$50),ROW($BF$1:$BF$46),""),ROW(BF1))),"") - which should have removed my blanks to create a continuous column.

The issue was that my 'blanks' weren't really blanks - they were zero length strings. Using the following formula in place of step 2 cracked the problem: IFERROR(INDEX($BF$5:$BF$50,SMALL(IF(LEN($BF$5:$BF$50)=0,"",ROW($BF$5:$BF$50)-MIN(ROW($BF$5:$BF$50))+1),ROW(BF1))),"")