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
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