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!

djclements's avatar
djclements
Silver Contributor
Feb 12, 2024

Andrew__K Here's a dynamic array formula that should do the trick:

 

=LET(
    data, Table1, wrap_count, 3,
    arr, TOCOL(WRAPCOLS(SEQUENCE(ROWS(data)), wrap_count), 2),
    WRAPROWS(TOCOL(SORTBY(data, arr)), COLUMNS(data)*wrap_count, "")
)

 

Please replace Table1 with your data range. Also, to include the header row with the results, try the following:

 

=LET(
    data, Table1, head, Table1[#Headers], wrap_count, 3,
    arr, TOCOL(WRAPCOLS(SEQUENCE(ROWS(data)), wrap_count), 2),
    VSTACK(
        TOROW(IF(SEQUENCE(wrap_count), head)),
        WRAPROWS(TOCOL(SORTBY(data, arr)), COLUMNS(data)*wrap_count, "")
    )
)

 

 

Please see the attached workbook, which also includes a custom LAMBDA function as an option...

Resources