Here, we will learn how to use the Excel dynamic array function: TOCOL. Later, we will also learn how to sort and extract unique values into column using TOCOL and the SORT and UNIQUE functions.
TOCOL function
The Excel TOCOL function transforms the given data into a single column, scanning values by row.
TOCOL takes three arguments: array, [ignore], [scan_by_column]. The first required argument array will take the range to transform. The second optional argument ignore will control what values the function can ignore. Specifying 1 will ignore blanks; 2 will ignore errors and 3 will ignore both blanks and errors. Omitting the argument will take the default value of 0 (zero) that will keep all the values. The third optional argument scan_by_column will control how the function will scan the values, by row or column. Specifying TRUE will scan the values by column. Omitting the argument will take the default value of FALSE that will scan the values by row.
Basic examples
By default, TOCOL will transform the given data into a single column, keeping all the values and scanning the values by row. The formula in E3 (in the image shown) is:
=TOCOL(A3:C5)
To ignore blanks or/and errors, the following formulas are applied in G3, I3, K3 respectively:
=TOCOL(A3:C5,1)
=TOCOL(A3:C5,2)
=TOCOL(A3:C5,3)
In M3, the function has been set to scan the values by column by specifying TRUE in the scan_by_column argument:
=TOCOL(A3:C5,3,TRUE)
Sorting and extracting unique values into column using TOCOL with SORT and UNIQUE
To transform a data into a single column and to sort and extract unique values from the same data, use a formula based on the TOCOL, SORT and UNIQUE functions.
The dataset (shown in the image) contains a data in three non-adjacent ranges. The goal is to join these ranges, sort the data alphabetically and extract unique values.
The formula in A8 is:
=SORT(UNIQUE(TOCOL((A3:B5,D3:E5,G3:H5))))
How this formula works
TOCOL joins the given three non-adjacent ranges (A3:B5,D3:E5,G3:H5) and transforms them into a single column. This result goes to UNIQUE that returns the unique values to SORT. Finally, SORT sorts the data alphabetically.
You have successfully learnt how to use the Excel dynamic array function: TOCOL, and how to sort and extract unique values into column using TOCOL and the SORT and UNIQUE functions! I hope this post helped you.
