Reshape one hour wide data to 15 min long data

[turbalejo]

Hi Excelers!,
I am trying to convert data for 30 sites quickly and need a way to reshape my one-hour wide data in kwH to 15 long data?
each sheet is one year/365 days.
Thank you.
Tony

 

[Toadstool]

Hi Turbalejo,

I'm not sure I've correctly interpreted your requirement but I think you want rows of 15 minute data totaled by hour in a columnar style.

I don't know what HE1, HE2 are so I've replaced with hourly heading times for 24 hours (so column M actually is copied through column AC).

Turbalejo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Time	kW		Date	0:00	1:00	2:00	3:00	4:00	5:00	6:00	7:00
2	1/1/2021	0:15	100		1/1/2021	364	868.56	652.68	0	0	0	0	0
3	1/1/2021	0:30	120		1/2/2021	0	0	0	0	0	0	0	0
4	1/1/2021	0:45	144		1/3/2021	1282	0	0	0	0	0	0	0
5	1/1/2021	1:00	172.8		1/4/2021	0	0	0	0	0	0	222	0
6	1/1/2021	1:15	207.36		1/5/2021	0	0	0	0	0	0	0	0
7	1/1/2021	1:30	222		1/6/2021	0	0	0	333	0	0	0	0
8	1/1/2021	1:45	266.4		1/7/2021	0	0	0	0	0	0	0	0
9	1/1/2021	2:00	319.68		1/8/2021	0	0	0	0	0	0	0	0
10	1/1/2021	2:15	333		1/9/2021	0	0	0	0	0	0	0	0
11	1/3/2021	0:00	380		1/10/2021	0	0	0	0	0	0	0	0
12	1/3/2021	0:15	402		1/11/2021	0	0	0	0	0	0	0	0
13	1/3/2021	0:30	500		1/12/2021	0	0	0	0	0	0	0	0
14	1/4/2021	6:12	222		1/13/2021	0	0	0	0	0	0	0	0
15	1/6/2021	3:59	333		1/14/2021	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
G1:M1	G1	=F1+TIME(1,0,0)
F2:M15	F2	=SUMIFS($C$2:$C$99999,$A$2:$A$99999,$E2,$B$2:$B$99999,">="&F$1,$B$2:$B$99999,"<"&F$1+TIME(1,0,0))
E3:E15	E3	=E2+1