Thursday, September 28, 2023

# Tip: Matrix to a column transpose

You have a table or a matrix composed of several columns and rows. Your objective is to transform it into a single continuous column using all the values from the matrix.

So, you have this: And you want this: How would you do it?

There isn’t a simple formula that I could find (or know of yet). Surely, you can do it manually relatively painlessly for a small matrix, but can it be done by a formula that can scale to much larger sets?

The solution:

After seeing and experimenting with different versions of solutions, I like this process the best.

Enter this formula for the first target cell of the destination column and copy it down…

`=INDEX(matrix,1+INT((ROW(A1)-1)/COLUMNS(matrix)),MOD(ROW(A1)-1+COLUMNS(matrix), COLUMNS(matrix))+1)`

The formula is doing this: matrix is the named range of T1 to T3 cells above. A1 is the 1st row and 1st column for reference, and the math is to ensure the offsetting is working as above. To keep the formula from getting more gnarly, there’s no actual counting of the size of the matrix; so the easiest way to do it is to pull down the formula in target column T Final until you get the first cell value “#REF!”. This error will indicate that all the cells from the matrix have been copied up to the previous row…in which case, you stop the copying and delete the cell with #REF error. And done!

Can you think of another formula (not GUI method, not VBA) that’s not as gnarly (e.g. Using OFFSET(), COUNTA(), etc.)?