TOCOL

TOCOL(array [,ignore] [,scan_by_column])

Returns the array transformed into a single column.

arrayThe array or reference to return as a column.
ignore(Optional) The number indicating which values to ignore:
0 = Keep all values (default)
1 = Ignore blanks
2 = Ignore errors
3 = Ignore blanks and errors
scan_by_column(Optional) A logical value indicating how the array will be scanned:
False (or 0) = Scanned by Row (default)
True (<> 0) = Scanned by Column

REMARKS
* This function was added in Microsoft 365.
* This function can create a Dynamic Array Formula.
* If "ignore" is left blank, then keep all values is used.
* If "scan_by_column" is left blank, then False is used.
* If any of the cells are blank, then they are padded with 0.
* You can use the TOROW function to return the array transformed into a single row.
* You can use the WRAPCOLS function to return the array transformed into multiple columns.
* You can use the WRAPROWS function to return the array transformed into multiple rows.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

 ABCDE
1=TOCOL(C1:D2) = {1 ; 2 ; 4 ; 5} 123
2=TOCOL(C1:D2,0) = {1 ; 2 ; 4 ; 5} 456
3=TOCOL(C1:D2,0,False) = {1 ; 2 ; 4 ; 5}    
4=TOCOL(C1:D2,1) = {1 ; 2 ; 4 ; 5} 123
5=TOCOL(C1:D2,1,True) = {1 ; 4 ; 2 ; 5} 4  
6=TOCOL(C4:E5) = {1 ; 2 ; 3 ; 4 ; 0 ; 0}    
7=TOCOL(C4:E5,0) = {1 ; 2 ; 3 ; 4 ; 0 ; 0}    
8=TOCOL(C4:E5,1) = {1 ; 2 ; 3 ; 4}    

1 - What is the range "C1:D2" transformed into a single column.
2 - What is the range "C1:D2" transformed into a single column, keeping all the values.
3 - What is the range "C1:D2" transformed into a single column, keeping all the values and scanning by row.
4 - What is the range "C1:D2" transformed into a single column, ignoring all blanks.
5 - What is the range "C1:D2" transformed into a single column, ignoring all blanks and scanning by column. The order has changed slightly.
6 - What is the range "C4:E5" transformed into a single column
7 - What is the range "C4:E5" transformed into a single column, keeping all the values.
8 - What is the range "C4:E5" transformed into a single column, ignoring all blanks.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top