Forum Discussion
Split Columns into multiple columns
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 Amazing! thank you very much for the solution. I'm not going to pretend I understand how you got there, but it will go a long way in increasing my understanding of these functions.
Are you able to demonstrate how TAKE/DROP would be used in your solution? as suggested by PeterBartholomew1. Would it reduce the calculations as you mentioned?
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!
- Perfect! Thanks so much for the solution, exactly what I needed.