Reorder the 3 column data into 2 columns of 24 rows

[konnursrikant]

I want to transform this data into two columns as Day 1 and Day 2 , starting with the day 1 first value and end day 1 for 24 hours later (2pm-2pm next day as day 1) and next 24 values as day 2. The start time for Day 1 keeps changing for different scenarios. I want a dynamic formula to Setup day 1 and day 2. I have 200+ such sheets. So a quick and easy process is much appreciated.

	Mon	Tue	Wed
	2023-01-30	2023-01-31	2023-02-01
00:00:00		179​	191​
01:00:00		109​	127​
02:00:00		93​	101​
03:00:00		144​	126​
04:00:00		255​	245​
05:00:00		727​	747​
06:00:00		1339​	1395​
07:00:00		1838​	1755​
08:00:00		1703​	1847​
09:00:00		1404​	1494​
10:00:00		1279​	1334​
11:00:00		1162​	1236​
12:00:00		1155​	1399​
13:00:00		1236​	1305​
14:00:00		1294​	1280​
15:00:00		1329​	1315​
16:00:00	1118​	1169​
17:00:00	1255​	1278​
18:00:00	1057​	1092​
19:00:00	883​	924​
20:00:00	666​	660​
21:00:00	474​	567​
22:00:00	482​	527​
23:00:00	306​	312​

 

[RobP]

Hi, Its a bit of a convoluted formula, and there maybe cleaner versions out there .., but here's one way to do it :

Rgds
Rob

Book1
	A	B	C	D	E	F	G	H	I
1
2	Mon	Tue	Wed				Day1	Day2
3		30/01/2023	31/01/2023	01/02/2023
4	00:00:00		179	191			1118	245
5	01:00:00		109	127			1255	747
6	02:00:00		93	101			1057	1395
7	03:00:00		144	126			883	1755
8	04:00:00		255	245			666	1847
9	05:00:00		727	747			474	1494
10	06:00:00		1339	1395			482	1334
11	07:00:00		1838	1755			306	1236
12	08:00:00		1703	1847			179	1399
13	09:00:00		1404	1494			109	1305
14	10:00:00		1279	1334			93	1280
15	11:00:00		1162	1236			144	1315
16	12:00:00		1155	1399
17	13:00:00		1236	1305
18	14:00:00		1294	1280
19	15:00:00		1329	1315
20	16:00:00	1118	1169
21	17:00:00	1255	1278
22	18:00:00	1057	1092
23	19:00:00	883	924
24	20:00:00	666	660
25	21:00:00	474	567
26	22:00:00	482	527
27	23:00:00	306	312
Sheet1

Cell Formulas
Range	Formula
G4:H15	G4	=LET(arrval,VSTACK(B4:B27,C4:C27,D4:D27),newval,FILTER(arrval,arrval<>0),dayone,TAKE(newval,12,1),daytwo,TAKE(newval,-12,1),result,HSTACK(dayone,daytwo),result)
Dynamic array formulas.

 

[konnursrikant]

Thank you very much!
Can I get a formula to pull the timing in column F as well ?

 

[RobP]

Hi, not sure to understand what you mean sorry ? What is in column F ?

 

[RobP]

sorry, I just realised I was taking 12 values, not 24 ... Correction below

=LET(arrval,VSTACK(B4:B27,C4:C27,D4:D27),newval,FILTER(arrval,arrval<>0),dayone,TAKE(newval,24,1),daytwo,TAKE(newval,-24,1),result,HSTACK(dayone,daytwo),result)

 

[konnursrikant]

Hi, not sure to understand what you mean sorry ? What is in column F ?

I want column A values in Column F with respect to the values in Column G, H

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Reorder the 3 column data into 2 columns of 24 rows
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.