Forum Discussion
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.
| Date/Time | Temp | |
| 1 | 01/02/2024 06:58:00 | 46.000 °C |
| 2 | 01/02/2024 06:59:00 | 44.000 °C |
| 3 | 01/02/2024 07:00:00 | 42.000 °C |
| 4 | 01/02/2024 07:01:00 | 40.500 °C |
| 5 | 01/02/2024 07:02:00 | 39.000 °C |
| 6 | 01/02/2024 07:03:00 | 37.500 °C |
| 7 | 01/02/2024 07:04:00 | 36.000 °C |
| 8 | 01/02/2024 07:05:00 | 34.500 °C |
| 9 | 01/02/2024 07:06:00 | 33.000 °C |
| 10 | 01/02/2024 07:07:00 | 32.000 °C |
| 11 | 01/02/2024 07:08:00 | 31.000 °C |
| 12 | 01/02/2024 07:09:00 | 30.000 °C |
I would like to spread the data across 6 columns (3x2), as per the example below. The data would populate top to bottom, left to right and then repeat after a page break.
I have added a column with the number in between to demonstrate where the data should end up.
| Date/Time | Temp | Date/Time | Temp | Date/Time | Temp | |||
| 1 | 01/02/2024 06:58:00 | 46.0 °C | 5 | 01/02/2024 07:02:00 | 39.0 °C | 9 | 01/02/2024 07:06:00 | 33.0 °C |
| 2 | 01/02/2024 06:59:00 | 44.0 °C | 6 | 01/02/2024 07:03:00 | 37.5 °C | 10 | 01/02/2024 07:07:00 | 32.0 °C |
| 3 | 01/02/2024 07:00:00 | 42.0 °C | 7 | 01/02/2024 07:04:00 | 36.0 °C | 11 | 01/02/2024 07:08:00 | 31.0 °C |
| 4 | 01/02/2024 07:01:00 | 40.5 °C | 8 | 01/02/2024 07:05:00 | 34.5 °C | 12 | 01/02/2024 07:09:00 | 30.0 °C |
This would allow me to include 3 times the amount of data per page when saving reports.
Is it possible to achieve this? Where would I start? Avoiding VBA.
Thank you
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!
To make it simpler, I think you can replace INDEX with WRAPROWS:
=WRAPROWS(TOROW(A2:C111,0,FALSE),9)
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...
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?
