Excel 2024: Reshaping an Array to a Vector and Back

You can unwind a rectangular range or array into a single column using TOCOL or a single row using TOROW. Each function can take an array or a range as the first argument. The optional Ignore argument can have Excel skip empty cells. The optional Scan_By_Column argument will control if values are read row-by-row (the default) or column-by-column.

Here is a comparison of three different ways to run TOCOL. The original array is in A2:B6 with an empty cell in B6.

If you simply ask for =TOCOL(A2:B6) as shown in D2 below, you will see that the empty cell shows up as a zero in D11. To prevent empty cells from showing up in the results, add a second argument of 1. Notice how the results in E2 do not include the zero for the empty cell.

Both of the results in D & E use the default of reading the data by rows. You start with History of A2, then Math from B2, then Comp. from A3, and so on. You can change the order of the results by including TRUE as the optional third argument as shown in F2. In this version of TOCOL, the results are History, Comp, Lunch, Physics, Shop from column A and Math, Econ, Lunch, Tech from column B.

What if you did not want Lunch in the results? Since Lunch is in the 3rd row of both columns, you could use =TOCOL(CHOOSEROWS(A2:B6,1,2,4,5)).

Once you have your results in a single column or row, you might like to rearrange it to a rectangular range. This can be done with WRAPCOLS or WRAPROWS. 

Getting all of the unique values from a rectangular range was previously very complicated. Today, using a combination of UNIQUE and TOCOL, it is easy. The figure below adds in a SORT function to make sure the results are sorted alphabetically.