Forum Discussion

Andrew__K's avatar
Andrew__K
Brass Contributor
Feb 12, 2024
Solved

Split Columns into multiple columns

I have sets of data generated from temperature data loggers. It spans two columns and up to 2000+ rows. One column is the date/time, the other column is the temperature data. Example below.     ...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Feb 13, 2024

    Andrew__K No worries. Here's how it can be modified using the TAKE/DROP method mentioned by PeterBartholomew1 to shorten the formula a bit:

     

    =LET(
    data, Table1, headers, Table1[#Headers], col_wrap, 3, row_wrap, 46,
    hdr, TOROW(IF(SEQUENCE(col_wrap), headers)),
    wrapCount, COLUMNS(data)*col_wrap,
    pageItems, col_wrap*row_wrap,
    pages, ROUNDUP(ROWS(data)/pageItems, 0),
    IFNA(DROP(REDUCE("", SEQUENCE(pages), LAMBDA(v,n, LET(
        a, TAKE(DROP(data, (n-1)*pageItems), pageItems),
        b, TOCOL(WRAPCOLS(SEQUENCE(ROWS(a)), col_wrap), 2),
        arr, WRAPROWS(TOCOL(SORTBY(a, b)), wrapCount),
        VSTACK(v, hdr, arr)))), 1), "")
)

     

    Note: you can also use Conditional Formatting to dynamically format the header rows. For example, in the attached file I used the formula =$A1="ID" to apply Bold font formatting to the output range $A$1:$I$1000 wherever "ID" is found in column A. Cheers!

Resources