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!
Just reshaping the array is a doddle. The problem with your requirement is that you also want to reorder the array to read {1, 5, 9, 2, 6, 10, ...} and, worse, even that changes as you add data.
Separating times and temperatures, one could use
= LET(
dtime, TOCOL(WRAPCOLS(EXPAND(datetime, 3*length,,""), length)),
tmp, TOCOL(WRAPCOLS(EXPAND(temp, 3*length,,""), length)),
WRAPROWS(TOCOL(HSTACK(dtime, tmp)), 6)
)or, scanning vertically mover the temperatures to the right as a block, CHOOSECOLS is used to sort the columns to give the alternating pattern of dates and temperatures.
= LET(
blocked, WRAPCOLS(TOCOL(EXPAND(data, 3*length,,""),,TRUE), length),
CHOOSECOLS(blocked, {1,4,2,5,3,6})
)In each case,
length
= 1 + QUOTIENT(ROWS(datetime), 3)
Thanks, PeterBartholomew1 djclements Lorenzo rachel
Some really great suggestions! The use of WRAPCOLS, WRAPROWS, etc... in these solutions has really opened my eyes to those functions and the ways they can be used.
The other challenge was the page break, ideally the data would spill across columns as you have demonstrated, then continue on the next page, left to right again. Rather than running 500+ rows down multiple pages and then spilling to the middle column back on page 1. Example below; again, the numbering has been added to demonstrate and is not part of the dataset.
Date/Time Temp Date/Time Temp Date/Time Temp 1 01/02/2024 06:58:00 46.000 °C 5 01/02/2024 07:02:00 39.000 °C 9 01/02/2024 07:06:00 33.000 °C 2 01/02/2024 06:59:00 44.000 °C 6 01/02/2024 07:03:00 37.500 °C 10 01/02/2024 07:07:00 32.000 °C 3 01/02/2024 07:00:00 42.000 °C 7 01/02/2024 07:04:00 36.000 °C 11 01/02/2024 07:08:00 31.000 °C 4 01/02/2024 07:01:00 40.500 °C 8 01/02/2024 07:05:00 34.500 °C 12 01/02/2024 07:09:00 30.000 °C --- PAGE BREAK ---
13 01/02/2024 07:10:00 46.000 °C 17 01/02/2024 07:14:00 39.000 °C 21 01/02/2024 07:18:00 33.000 °C 14 01/02/2024 07:11:00 44.000 °C 18 01/02/2024 07:15:00 37.500 °C 22 01/02/2024 07:19:00 32.000 °C 15 01/02/2024 07:12:00 42.000 °C 19 01/02/2024 07:16:00 36.000 °C 23 01/02/2024 07:20:00 31.000 °C 16 01/02/2024 07:13:00 40.500 °C 20 01/02/2024 07:17:00 34.500 °C 24 01/02/2024 07:21:00 30.000 °C With your suggested solutions, a data set of say 2000 rows would need to extend 700+ rows on the left side from say page 1 to page 15 and then continue in the middle from page 1 to page 15, etc...
Is it possible to limit the rows to say 40... so there are 3 x 40 rows per page and then continue on the next page?
Without a procedural language such as VBA or a Pivot Table you will not be able to 'spill' a formula output from sheet to sheet. Therefore you need a separate formula on each sheet, perhaps looking something like
= Displayλ(data, sheet, recordsPerSheet)where 'sheet' could be a number read from the current sheet or hard-wired into the function argument. To get the appropriate block of data for display you could use TAKE/DROP.
= TAKE(DROP(data, (sheet-1)*recordsPerSheet), recordsPerSheet)PeterBartholomew1 Nice use of TAKE/DROP here to get the blocks of data, in particular for the last block, as TAKE doesn't error when using a greater number of rows than the rows remaining in the data set. I was using CHOOSEROWS with SEQUENCE, but that method requires additional calculations to get the exact number of rows remaining in the last block, otherwise it will error if the number of rows remaining are less than the block size. TAKE/DROP gets the job done without the additional calculations. Two thumbs up!
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?
