by 
How to use TOCOL function in Excel - formula examples
To gain more understanding of the possibilities of the TOCOL function and what tasks it can cover, let's take a look at some formula examples.
Transform array to column ignoring blanks and errors
As you may have noticed in the previous example, the default TOCOL formula keeps all the values from the source array, including blank cells and errors.
In the resulting array, empty cells are represented by zeros, which may be quite confusing, especially if the original array has 0 values. The solution is to skip blanks. For this, you set the 2nd argument to 1:
=TOCOL(A2:C5, 1)
To ignore errors, set the 2nd argument to 2:
=TOCOL(A2:C5, 2)
To exclude both, blanks and errors, use 3 for the ignore argument:
=TOCOL(A2:C5, 3)
Scan array horizontally or vertically
With the default scan_by_column argument (FALSE or omitted), the TOCOL function scans the array horizontally by row. To process values by column, set this argument to TRUE or 1. For example:
=TOCOL(A2:C5, ,TRUE)
Notice that, in both cases, the returned arrays are the same size, but the values are arranged in a different order.
Combine multiple ranges into one column
If you are dealing with several non-contiguous ranges, then you can first combine the ranges vertically into a single array with the help of the VSTACK function, and then use TOCOL to transform the combined array into a column.
Assuming the first range is A2:C4 and the second range is A8:C9, the formula takes this form:
=TOCOL(VSTACK(A2:C4, A8:C9))
This formula demonstrates the default behavior - reads the combined arrays horizontally from left to right as shown in column E in the image below.
To read values vertically from top to bottom, you set the 3rd argument of TOCOL to TRUE:
=TOCOL(VSTACK(A2:C4, A8:C9), ,TRUE)
Please pay attention that, in this case, the formula first returns values from column A of both arrays, then from column B, and so on. The reason is that TOCOL scans a single stacked array, not the original individual ranges.
If your business logic requires stacking the original ranges horizontally rather than vertically, then use the HSTACK function instead of VSTACK.
To append each subsequent array to the right of the previous array and read the combined arrays horizontally, the formula is:
=TOCOL(HSTACK(A2:C4, A8:C10))
To add each subsequent array to the right of the previous array and scan the combined arrays vertically, the formula is:
=TOCOL(HSTACK(A2:C4, A8:C10), ,TRUE)
Extract unique values from a multi-column range
The Excel UNIQUE function can easily find uniques in a single column or row as well as return unique rows, but it cannot extract unique values from a multi-column array. The solution is to use it together with the TOCOL function.
For instance, to extract all the different (distinct) values from the range A2:C7, the formula is:
=UNIQUE(TOCOL(A2:C7))
Additionally, you can wrap the above formula in the SORT function to arrange the returned array in alphabetic order:
=SORT(UNIQUE(TOCOL(A2:C7)))
