How to use the TOCOL function
The TOCOL function lets you rearrange values in 2D cell ranges to a single column.
Dynamic array formula in cell E3:
The TOCOL function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
- TOCOL Function Syntax
- TOCOL Function Arguments
- TOCOL Function example
- TOCOL Function example - by column
- TOCOL Function - blanks and errors
- TOCOL Function alternative
- TOCOL Function - multiple cell ranges as source
- Extract unique distinct values from a cell range
- Extract unique distinct values from multiple cell ranges
1. TOCOL Function Syntax
TOCOL(array, [ignore], [scan_by_col])
2. TOCOL Function Arguments
array | Required. The source cell range or array. Use parentheses and comma delimiter to add more arrays or cell ranges. See section 7 below for an example. |
[ignore] | Optional. Ignore specified values. 0 - keep all values (default) 1 - ignore blanks 2 - ignore errors 3- ignore blanks and errors |
[scan_by_col] | Optional. How the function fetches the values from the source. FALSE - by row (default). TRUE - by column. |
3. TOCOL Function example
The image above demonstrates how the TOCOL function rearranges the values to fit a single column, and that it is fetching values by row in its default state.
Dynamic array formula in cell E4:
3.1 Explaining formula
Step 1 - TOCOL function
TOCOL(array, [ignore], [scan_by_col])
Step 2 - Populate arguments
array - B2:E4
Step 3 - Evaluate function
TOCOL(B2:E4)
becomes
TOCOL({89, 68, 19, 37;
27, 84, 92, 63;
26, 98, 62, 100})
and returns
{89; 68; 19; 37; 27; 84; 92; 63; 26; 98; 62; 100}
4. TOCOL Function example - by column
The image above demonstrates how the TOCOL function rearranges the values to fit a single column, this example shows it fetching values column by column. Default value is FALSE - by row.
Dynamic array formula in cell E4:
4.1 Explaining formula
Step 1 - TOCOL function
TOCOL(array, [ignore], [scan_by_col])
Step 2 - Populate arguments
array - B2:E4
[scan_by_col]) - TRUE
Step 3 - Evaluate function
TOCOL(B2:E4, , TRUE)
becomes
TOCOL({89, 68, 19, 37;
27, 84, 92, 63;
26, 98, 62, 100})
and returns
{89; 68; 19; 37; 27; 84; 92; 63; 26; 98; 62; 100}
5. TOCOL Function - blanks and errors
The image above demonstrates what happens when your source data has empty values and errors.
The result is an array containing a 0 (zero) located at the empty values and the error values are kept.
Dynamic array formula in cell E4:
The image below shows how to deal with blanks and error values.
You can ignore blank and error values using the second argument.
Here are all valid numbers for the second argument:
0 - keep all values (default)
1 - ignore blanks
2 - ignore errors
3- ignore blanks and errors
Dynamic array formula in cell E4:
5.1 Explaining formula
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:E4, 3)
becomes
TOCOL({89, 68, 19, 37;
27, 0, 92, 63;
26, 98, 62, #DIV/0!}
)
and returns
{89; 68; 19; 37; 27; 92; 63; 26; 98; 62}
6. TOCOL alternative
There are no great alternative formulas for earlier Excel versions. Here are a few links:
- Rearrange values in a cell range to a single column
- Combine cell ranges ignore blank cells
- Merge two columns
- Merge three columns into one list
7. TOCOL function - multiple cell ranges as source
You can consolidate values across worksheets if you combine the VSTACK function and the TOCOL function, the formula spills values into a single column.
The image above shows how to combine values from cell ranges B2:C3, E2:F3, and H2:I3 into a single column.
Dynamic array formula in cell B8:
Update! The TOCOL function accepts multiple references in the array argument. There is no need for the VSTACK function. Here is how:
The parentheses and a delimiting comma let you use multiple non-adjacent cell ranges.
7.1 Explaining formula
Step 1 - Stack values from multiple sources
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array.
VSTACK(array1,[array2],...)
VSTACK(B2:C3, E2:F3, H2:I3)
becomes
VSTACK({89,68;27,84}, {19,37;92,63}, {26,98;62,100})
and returns
{89,68;
27,84;
19,37;
92,63;
26,98;
62,100}
Step 2 - Rearrange values into one column
TOCOL(VSTACK(B2:C3, E2:F3, H2:I3))
becomes
TOCOL({89,68;
27,84;
19,37;
92,63;
26,98;
62,100})
and returns
{89; 68; 27; 84; 19; 37; 92; 63; 26; 98; 62; 100}
8. Extract unique distinct values from a multi-column cell range
The UNIQUE function doesn't let you extract unique distinct values if your source data has multiple columns, it will return unique distinct rows instead.
However, the TOCOL function allows you to rearrange the values to a single column array, and that lets you extract unique distinct values.
Dynamic array formula in cell B8:
8.1 Explaining formula
Step 1 - Rearrange values
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:G5)
becomes
TOCOL({"Elizabeth","Patricia","Elizabeth","Patricia","Jennifer","William"; "John","John","Robert","Patricia","William","Mary"; "Michael","Elizabeth","Linda","Linda","Michael","Jennifer"; "Linda","William","William","Patricia","Linda","Mary"})
and returns
{"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"}.
The column delimiting character changed from a comma to a semicolon in the array above. This means that each value is in a new row, in other words, values are rearranged to fit a single column.
Step 2 - Extract unique distinct values
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(B2:G5))
becomes
UNIQUE({"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"})
and returns
{"Elizabeth";"Patricia";"Jennifer";"William";"John";"Robert";"Mary";"Michael";"Linda"}.
9. Extract unique distinct values from multiple cell ranges
You can consolidate values across worksheets, rearrange values so they fit a single column, and then extract unique distinct values.
The image above shows a formula that extracts unique distinct values from three different non-adjacent cell ranges.
Dynamic array formula in cell B8:
Update!
No need to use the HSTACK function. Use the comma as a union operator, it combines multiple cell ranges.
9.1 Explaining formula
Step 1 - Rearrange values
The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)
HSTACK(array1,[array2],...)
HSTACK(B2:C5, E2:F5, H2:I5)
becomes
HSTACK({"Elizabeth", "Patricia"; "John", "John"; "Michael", "Elizabeth"; "Linda", "William"}, {"Elizabeth", "Patricia"; "Robert", "Patricia"; "Linda", "Linda"; "William", "Patricia"}, {"Jennifer", "William"; "William", "Mary"; "Michael", "Jennifer"; "Linda", "Mary"})
and returns
{"Elizabeth", "Patricia", "Elizabeth", "Patricia", "Jennifer", "William"; "John", "John", "Robert", "Patricia", "William", "Mary"; "Michael", "Elizabeth", "Linda", "Linda", "Michael", "Jennifer"; "Linda", "William", "William", "Patricia", "Linda", "Mary"}
Step 2 - Rearrange values to one column
TOCOL(array, [ignore], [scan_by_col])
TOCOL(HSTACK(B2:C5, E2:F5, H2:I5))
becomes
TOCOL({"Elizabeth", "Patricia", "Elizabeth", "Patricia", "Jennifer", "William"; "John", "John", "Robert", "Patricia", "William", "Mary"; "Michael", "Elizabeth", "Linda", "Linda", "Michael", "Jennifer"; "Linda", "William", "William", "Patricia", "Linda", "Mary"})
and returns
{"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"}.
Step 3 - Extract unique distinct values
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(HSTACK(B2:C5, E2:F5, H2:I5)))
becomes
UNIQUE({"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"})
and returns
{"Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "Robert"; "Mary"; "Michael"; "Linda"}.
'TOCOL' function examples
The following 14 articles contain the TOCOL function.
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Functions in this article
Functions in 'Array manipulation' category
The TOCOL function function is one of many functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form