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!
Andrew__K Yes, I didn't think about that until after posting my first formula... it wraps the data in order by output column. To break it up into pages, the REDUCE function can be used to iterate through the data in blocks of rows using the CHOOSEROWS function (plus a bunch of intermediary calculations):
=LET(
data, Table1, headers, Table1[#Headers], col_wrap, 3, row_wrap, 46,
hdr, TOROW(IF(SEQUENCE(col_wrap), headers)),
rws, ROWS(data),
cols, COLUMNS(data),
pageItems, col_wrap*row_wrap,
pages, ROUNDUP(rws/pageItems, 0),
rem, MOD(rws, pageItems),
lastPage, IF(rem, rem, pageItems),
IFNA(DROP(REDUCE("", SEQUENCE(pages), LAMBDA(v,n, LET(
r, IF(n=pages, lastPage, pageItems),
a, CHOOSEROWS(data, SEQUENCE(r,, (n-1)*pageItems+1)),
b, TOCOL(WRAPCOLS(SEQUENCE(r), col_wrap), 2),
arr, WRAPROWS(TOCOL(SORTBY(a, b)), cols*col_wrap),
VSTACK(v, hdr, arr)))), 1), "")
)
Adjust the row_wrap amount as needed, in order to fit to one page (46 worked for me with my page setup and printer settings).
Please see the attached workbook, which also contains the updated WRAPTABLE custom function...
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.