TOCOL function

The TOCOL function in Jspreadsheet Formulas Pro is a helpful tool that transforms an array into a single-column array. Essentially, it takes a group of data points and rearranges them into one vertical line. This is useful when you want to simplify or reorganize your data for easier reading and analysis. With the TOCOL function, you can streamline your data management tasks in Jspreadsheet.

Documentation

Converts an array into a single-column array.

Syntax

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

Parameter	Description
`array`	The array to be converted into a single-column array.
`[ignore]`	Optional. A boolean value (TRUE or FALSE) that determines whether to ignore empty cells in the input array. By default, empty cells are not ignored (FALSE).
`[scan_by_column]`	Optional. A boolean value (TRUE or FALSE) that determines whether to scan the array by column (TRUE) or by row (FALSE). By default, scanning is done by column (TRUE).

Behavior

The TOCOL function is typically used for returning the column number of a cell reference. Here's how it generally handles different types of inputs:

Empty Cells: Controlled by the [ignore] parameter. If TRUE, empty cells are skipped; if FALSE, they are included in the output column.
Text: The TOCOL function works with text as well. It will return the column number of the cell which contains the text. The content of the cell does not affect the outcome of the function.
Booleans: The function treats boolean values the same way as it treats text or numbers. It will return the column number of the cell which contains the boolean value.
Errors: If the cell reference provided to the TOCOL function is invalid or non-existent, the function will return an error.

Common Errors

Error Name	Description
#N/A	May occur if the specified input range does not exist.
#VALUE!	Occurs if a non-array or invalid argument is passed.

Best practices

Always ensure to provide a valid cell reference to the TOCOL function to avoid errors.

Use the [ignore] argument when you want to exclude blank cells from the output.

Use [scan_by_column] = TRUE if your data is structured in columns; set it to FALSE if your data is structured by rows.

Keep ranges tidy to avoid including unwanted blanks or errors in the output column.

Try to keep your spreadsheet clean and organized. Having a well-structured spreadsheet makes it easier to use functions like TOCOL effectively.

Usage

A few examples using the TOCOL function.

TOCOL(A1:B5)  
→ Returns a single column with all values from the range A1:B5.  

TOCOL(A1:B5, TRUE)  
→ Returns a single column with all values from A1:B5 but skips empty cells.  

TOCOL(A1:C3, FALSE, FALSE)  
→ Returns a single column scanning row by row instead of column by column.  

TOCOL({1,2,3;4,5,6})  
→ Returns a single column: {1;2;3;4;5;6}

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjhiOGFkNGJlZjE0YWQ2YjU1ZjE3YWMyZWQ5N2E0MDFjZmJlYWFiMTA3ZTgwNWM4MWVkMjRhZGM3NzkxM2FkMTUyZWU1YjZiYmMxODc3YWE5NmVkODk3ODBkOWQ0ZGM3OTRmN2NiYzk3OWI1NDcxM2NjOTUwMDk2ODg1MDQ0ZGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product A",
        "Product B",
        "Product C"
    ],
    [
        100,
        150,
        200
    ],
    [
        75,
        125,
        175
    ],
    [
        90,
        140,
        190
    ],
    [
        "=TOCOL(A1:C4)"
    ],
    [
        "Product A"
    ],
    [
        100
    ],
    [
        75
    ],
    [
        90
    ],
    [
        "Product B"
    ],
    [
        150
    ],
    [
        125
    ],
    [
        140
    ],
    [
        "Product C"
    ],
    [
        200
    ],
    [
        175
    ],
    [
        190
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('MjhiOGFkNGJlZjE0YWQ2YjU1ZjE3YWMyZWQ5N2E0MDFjZmJlYWFiMTA3ZTgwNWM4MWVkMjRhZGM3NzkxM2FkMTUyZWU1YjZiYmMxODc3YWE5NmVkODk3ODBkOWQ0ZGM3OTRmN2NiYzk3OWI1NDcxM2NjOTUwMDk2ODg1MDQ0ZGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Product A",
        "Product B",
        "Product C"
    ],
    [
        100,
        150,
        200
    ],
    [
        75,
        125,
        175
    ],
    [
        90,
        140,
        190
    ],
    [
        "=TOCOL(A1:C4)"
    ],
    [
        "Product A"
    ],
    [
        100
    ],
    [
        75
    ],
    [
        90
    ],
    [
        "Product B"
    ],
    [
        150
    ],
    [
        125
    ],
    [
        140
    ],
    [
        "Product C"
    ],
    [
        200
    ],
    [
        175
    ],
    [
        190
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('MjhiOGFkNGJlZjE0YWQ2YjU1ZjE3YWMyZWQ5N2E0MDFjZmJlYWFiMTA3ZTgwNWM4MWVkMjRhZGM3NzkxM2FkMTUyZWU1YjZiYmMxODc3YWE5NmVkODk3ODBkOWQ0ZGM3OTRmN2NiYzk3OWI1NDcxM2NjOTUwMDk2ODg1MDQ0ZGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product A",
        "Product B",
        "Product C"
    ],
    [
        100,
        150,
        200
    ],
    [
        75,
        125,
        175
    ],
    [
        90,
        140,
        190
    ],
    [
        "=TOCOL(A1:C4)"
    ],
    [
        "Product A"
    ],
    [
        100
    ],
    [
        75
    ],
    [
        90
    ],
    [
        "Product B"
    ],
    [
        150
    ],
    [
        125
    ],
    [
        140
    ],
    [
        "Product C"
    ],
    [
        200
    ],
    [
        175
    ],
    [
        190
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MjhiOGFkNGJlZjE0YWQ2YjU1ZjE3YWMyZWQ5N2E0MDFjZmJlYWFiMTA3ZTgwNWM4MWVkMjRhZGM3NzkxM2FkMTUyZWU1YjZiYmMxODc3YWE5NmVkODk3ODBkOWQ0ZGM3OTRmN2NiYzk3OWI1NDcxM2NjOTUwMDk2ODg1MDQ0ZGEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Product A",
        "Product B",
        "Product C"
    ],
    [
        100,
        150,
        200
    ],
    [
        75,
        125,
        175
    ],
    [
        90,
        140,
        190
    ],
    [
        "=TOCOL(A1:C4)"
    ],
    [
        "Product A"
    ],
    [
        100
    ],
    [
        75
    ],
    [
        90
    ],
    [
        "Product B"
    ],
    [
        150
    ],
    [
        125
    ],
    [
        140
    ],
    [
        "Product C"
    ],
    [
        200
    ],
    [
        175
    ],
    [
        190
    ]
]
            }]
        });
    }
}

Edit this page on GitHub