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!
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!