Formatting

[motherboard85]

Hi I have this sheet showing utility consumption every 30 mins. What i would like to do is format this so each 30 min period is down the left hand column with the data and the corresponding consumption is next to that period. Like the second picture. Is there a quicker way to this than manually?

 

[motherboard85]

 

[awoohaw]

Try this. I had to truncate the hours columns due to size limitations of xl2bb, but this should work for you.

Book4
	A	B	C	D	E	F	G	H
1		00:30:00	01:00:00	01:30:00	02:00:00	02:30:00	03:00:00	03:30:00
2	01-01-2024	28	44	33	71	18	55	32
3	02-01-2024	55	41	55	98	52	76	68
4	03-01-2024	21	84	72	68	13	20	9
5	04-01-2024	33	87	86	56	47	67	40
6	05-01-2024	88	57	65	62	10	98	21
7	06-01-2024	98	41	33	95	86	99	22
8	07-01-2024	97	7	94	1	7	34	35
9	08-01-2024	16	96	28	53	9	41	44
10	09-01-2024	32	6	38	97	69	39	89
11	10-01-2024	95	10	91	39	37	98	57
12	11-01-2024	16	11	92	94	45	19	31
13	12-01-2024	8	27	41	22	69	58	51
14	13-01-2024	11	10	28	88	38	79	12
15	14-01-2024	61	99	76	26	82	38	55
16	15-01-2024	59	18	14	39	5	79	99
17	16-01-2024	56	91	93	24	92	11	30
18	17-01-2024	27	63	92	28	98	55	19
19	18-01-2024	55	54	7	99	19	94	59
20	19-01-2024	70	61	85	95	43	0	29
21	20-01-2024	56	97	59	24	52	68	76
22	21-01-2024	60	85	98	58	84	26	4
23	22-01-2024	3	42	58	5	10	87	83
24	23-01-2024	85	88	61	1	6	0	68
25	24-01-2024	31	21	29	0	66	79	22
26	25-01-2024	92	58	38	50	69	97	72
27	26-01-2024	90	21	13	10	65	82	59
28	27-01-2024	4	58	11	88	32	52	71
29	28-01-2024	47	90	83	2	32	8	96
30	29-01-2024	91	68	53	94	48	72	48
31	30-01-2024	89	30	63	53	65	91	58
32	31-01-2024	85	96	42	95	74	46	66
33	01-02-2024	75	9	79	4	48	27	84
34
35
36
37
38	01-01-2024 00:30:00	28		2024-01-01 00:30				28
39	01-01-2024 01:00:00	44
40	01-01-2024 01:30:00	33
41	01-01-2024 02:00:00	71
42	01-01-2024 02:30:00	18
43	01-01-2024 03:00:00	55
44	01-01-2024 03:30:00	32
45	01-01-2024 04:00:00	3
46	01-01-2024 04:30:00	96
47	01-01-2024 05:00:00	23
48	01-01-2024 05:30:00	63
49	01-01-2024 06:00:00	94
50	01-01-2024 06:30:00	87
51	01-01-2024 07:00:00	10
52	01-01-2024 07:30:00	68
53	01-01-2024 08:00:00	7
54	01-01-2024 08:30:00	97
55	01-01-2024 09:00:00	89
56	01-01-2024 09:30:00	82
57	01-01-2024 10:00:00	90
58	01-01-2024 10:30:00	14
59	01-01-2024 11:00:00	43
60	01-01-2024 11:30:00	26
61	01-01-2024 12:00:00	75
62	01-01-2024 12:30:00	7
63	01-01-2024 13:00:00	7
64	01-01-2024 13:30:00	10
65	01-01-2024 14:00:00	95
66	01-01-2024 14:30:00	89
67	01-01-2024 15:00:00	93
68	01-01-2024 15:30:00	20
69	01-01-2024 16:00:00	29
70	01-01-2024 16:30:00	4
71	01-01-2024 17:00:00	75
72	01-01-2024 17:30:00	87
73	01-01-2024 18:00:00	67
74	01-01-2024 18:30:00	42
75	01-01-2024 19:00:00	7
76	01-01-2024 19:30:00	65
77	01-01-2024 20:00:00	78
78	01-01-2024 20:30:00	93
79	01-01-2024 21:00:00	67
80	01-01-2024 21:30:00	55
81	01-01-2024 22:00:00	12
82	01-01-2024 22:30:00	39
83	01-01-2024 23:00:00	91
84	01-01-2024 23:30:00	26
85	02-01-2024 00:00:00	94
86	02-01-2024 00:30:00	55
87	02-01-2024 01:00:00	41
88	02-01-2024 01:30:00	55
89	02-01-2024 02:00:00	98
90	02-01-2024 02:30:00	52
91	02-01-2024 03:00:00	76
92	02-01-2024 03:30:00	68
93	02-01-2024 04:00:00	16
94	02-01-2024 04:30:00	83
95	02-01-2024 05:00:00	0
96	02-01-2024 05:30:00	70
97	02-01-2024 06:00:00	58
98
Sheet1

Cell Formulas
Range	Formula
A38:A97	A38	=INDEX($A$2:$A$33,ROUNDUP((ROW(A38)-37)/48,0))+ INDEX($B$1:$AW$1,1,MOD((ROW(A38)-38),48)+1)
B38:B97	B38	=INDEX($B$2:$AW$33,ROUNDUP((ROW($A38)-37)/48,0),MOD((ROW($A38)-38),48)+1)
D38	D38	=INDEX($A$2:$A$33,ROUNDUP((ROW(A38)-37)/48,0))+ INDEX($B$1:$AW$1,1,MOD((ROW(A38)-38),48)+1)
H38	H38	=INDEX($B$2:$AW$33,ROUNDUP((ROW($A$38)-37)/48,0),MOD((ROW($A$38)-38),48)+1)

 

[motherboard85]

HI thanks im working with it but struggling to get it to work on my sheet seems to start on a random date and not the first date on the list

 

[awoohaw]

Well, that wasn't in your requirement.
I'm not at computer now. I'll have to do it later or someone else can certainly chime in.