Difficulty transposing and then stacking time series data

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
I run a daily report and the data sources are all organized differently. For this particular example, my goal is to drop the data in cell A1, run a macro, and auto-clean/organize accordingly. Below is the data as it is entered (note the date range (number of rows) can differ, steps I believe need to be taken, and the end result I am looking for.

Beginning Data:

OperatingDateZoneDataTypeTotal
0:00​
1:00​
2:00​
3:00​
4:00​
5:00​
6:00​
7:00​
8:00​
9:00​
10:00​
11:00​
12:00​
13:00​
14:00​
15:00​
16:00​
17:00​
18:00​
19:00​
20:00​
21:00​
22:00​
23:00​
5/13/2020​
LZ_HOUSTONForecast (without Losses)
337.6​
11.1​
9.8​
8.9​
8.2​
7.9​
8.4​
9.2​
8.9​
8.9​
9.5​
11.3​
13​
14.9​
16.8​
18.3​
19.7​
20.8​
21.4​
21.4​
19.9​
19.6​
18.8​
16.8​
14​
5/13/2020​
LZ_HOUSTONForecast (with Losses)
361.3​
11.9​
10.5​
9.6​
8.7​
8.5​
8.9​
9.8​
9.5​
9.5​
10.2​
12.1​
13.9​
16​
18​
19.5​
21.1​
22.3​
23​
22.9​
21.3​
21​
20.1​
18​
15​
5/13/2020​
LZ_HOUSTONActual Load (without Losses)
350.12​
11.35​
10.15​
9.19​
8.58​
8.06​
7.65​
7.59​
8.06​
9.2​
10.71​
12.76​
15.21​
17.66​
19.33​
20.17​
20.81​
21.59​
21.8​
21.55​
20.21​
19.3​
18.23​
16.57​
14.38​
5/13/2020​
LZ_HOUSTONActual Load (with Losses)
374.85​
11.96​
10.73​
9.75​
9.14​
8.56​
8.14​
8.07​
8.6​
9.88​
11.56​
13.73​
16.3​
18.82​
20.61​
21.62​
22.31​
23.16​
23.32​
23.12​
21.68​
20.72​
19.67​
17.87​
15.51​
5/13/2020​
LZ_NORTHForecast (without Losses)
239.5​
7.7​
6.6​
6​
5.7​
5.6​
6.2​
7.6​
8.5​
7.5​
7.3​
7.7​
8.5​
9.2​
10.2​
11.1​
12.2​
13.7​
14.7​
15.3​
14.9​
14.6​
14.7​
13.1​
10.8​
5/13/2020​
LZ_NORTHForecast (with Losses)
256.2​
8.2​
7​
6.5​
6.1​
6​
6.6​
8.1​
9.1​
8​
7.8​
8.3​
9.1​
9.9​
10.9​
11.9​
13.1​
14.6​
15.8​
16.4​
15.9​
15.7​
15.7​
14​
11.5​
5/13/2020​
LZ_NORTHActual Load (without Losses)
229.33​
6.94​
5.98​
5.33​
4.97​
4.88​
4.85​
5.21​
6.03​
6.91​
7.59​
8.39​
8.97​
9.56​
10​
10.73​
11.87​
13.12​
14.84​
15.71​
15.32​
14.54​
14.04​
12.68​
10.88​
5/13/2020​
LZ_NORTHActual Load (with Losses)
244.1​
7.26​
6.27​
5.61​
5.25​
5.14​
5.11​
5.5​
6.38​
7.37​
8.14​
8.97​
9.55​
10.13​
10.6​
11.44​
12.66​
14​
15.79​
16.76​
16.34​
15.52​
15.06​
13.6​
11.65​
5/13/2020​
LZ_SOUTHForecast (without Losses)
38.5​
1.3​
1.2​
1.1​
1.1​
1.1​
1.1​
1.3​
1.3​
1.2​
1.3​
1.4​
1.5​
1.6​
1.7​
1.8​
2​
2.1​
2.2​
2.2​
2.2​
2.1​
2.1​
1.9​
1.6​
5/13/2020​
LZ_SOUTHForecast (with Losses)
41.2​
1.4​
1.3​
1.2​
1.2​
1.1​
1.2​
1.4​
1.4​
1.3​
1.4​
1.5​
1.6​
1.7​
1.9​
2​
2.1​
2.3​
2.4​
2.4​
2.3​
2.3​
2.3​
2​
1.7​
5/13/2020​
LZ_SOUTHActual Load (without Losses)
27.34​
0.85​
0.74​
0.68​
0.59​
0.6​
0.6​
0.6​
0.7​
0.76​
0.81​
0.92​
1.06​
1.16​
1.27​
1.43​
1.52​
1.66​
1.84​
1.85​
1.8​
1.66​
1.59​
1.43​
1.22​
5/13/2020​
LZ_SOUTHActual Load (with Losses)
29.41​
0.89​
0.78​
0.72​
0.64​
0.64​
0.64​
0.64​
0.74​
0.82​
0.88​
1​
1.14​
1.24​
1.36​
1.55​
1.64​
1.79​
1.97​
2​
1.93​
1.79​
1.72​
1.55​
1.32​
5/13/2020​
LZ_WESTForecast (without Losses)
58.8​
2.2​
2.2​
2.1​
2.1​
2.1​
2.2​
2.3​
2.4​
2.4​
2.4​
2.5​
2.5​
2.6​
2.6​
2.7​
2.7​
2.7​
2.7​
2.7​
2.7​
2.6​
2.6​
2.5​
2.4​
5/13/2020​
LZ_WESTForecast (with Losses)
63​
2.4​
2.3​
2.3​
2.2​
2.2​
2.3​
2.5​
2.6​
2.6​
2.6​
2.7​
2.7​
2.8​
2.8​
2.8​
2.9​
2.9​
2.9​
2.9​
2.8​
2.8​
2.8​
2.7​
2.5​
5/13/2020​
LZ_WESTActual Load (without Losses)
9.38​
0.32​
0.27​
0.23​
0.21​
0.2​
0.2​
0.21​
0.25​
0.26​
0.28​
0.29​
0.32​
0.36​
0.41​
0.49​
0.56​
0.63​
0.68​
0.7​
0.62​
0.55​
0.53​
0.44​
0.37​
5/13/2020​
LZ_WESTActual Load (with Losses)
10.03​
0.33​
0.28​
0.24​
0.23​
0.21​
0.21​
0.22​
0.27​
0.28​
0.3​
0.32​
0.34​
0.39​
0.44​
0.52​
0.6​
0.67​
0.73​
0.75​
0.67​
0.59​
0.57​
0.48​
0.4​
5/14/2020​
LZ_HOUSTONForecast (without Losses)
344.7​
11.9​
10.5​
9.5​
8.8​
8.6​
8.9​
9.5​
9.2​
9.4​
9.8​
11.7​
13.3​
15.3​
17​
18.2​
19.5​
20.4​
21​
20.9​
20​
19.8​
19.2​
17.3​
14.7​
5/14/2020​
LZ_HOUSTONForecast (with Losses)
368.8​
12.8​
11.2​
10.2​
9.5​
9.2​
9.6​
10.2​
9.9​
10​
10.5​
12.5​
14.2​
16.4​
18.2​
19.5​
20.8​
21.8​
22.4​
22.4​
21.4​
21.2​
20.6​
18.5​
15.8​
5/14/2020​
LZ_HOUSTONActual Load (without Losses)
388.43​
12.6​
11.21​
10.23​
9.52​
9.02​
8.65​
8.57​
9.2​
10.51​
12.47​
14.49​
16.67​
18.82​
20.73​
22.49​
24.03​
24.79​
25.31​
24.54​
22.53​
20.86​
19.39​
17.22​
14.59​
5/14/2020​
LZ_HOUSTONActual Load (with Losses)
416.85​
13.52​
12.07​
11.04​
10.3​
9.77​
9.37​
9.28​
9.99​
11.41​
13.56​
15.66​
17.92​
20.2​
22.25​
24.09​
25.72​
26.5​
27.12​
26.26​
23.98​
22.08​
20.69​
18.4​
15.66​
5/14/2020​
LZ_NORTHForecast (without Losses)
269.7​
8.6​
7.4​
6.8​
6.4​
6.3​
6.9​
8.3​
9.1​
8.2​
8​
8.9​
10.1​
11.2​
12.3​
13.3​
14.3​
15.6​
16.4​
16.9​
16.4​
16​
16.1​
14.4​
12​
5/14/2020​
LZ_NORTHForecast (with Losses)
288.6​
9.2​
7.9​
7.3​
6.8​
6.7​
7.3​
8.8​
9.8​
8.8​
8.6​
9.5​
10.8​
12​
13.2​
14.2​
15.3​
16.7​
17.5​
18​
17.5​
17.1​
17.2​
15.4​
12.8​
5/14/2020​
LZ_NORTHActual Load (without Losses)
294.59​
9.15​
7.87​
7.01​
6.47​
6.07​
5.79​
5.92​
6.6​
7.55​
8.55​
9.82​
11.65​
13.34​
14.76​
16.08​
17.55​
18.9​
20.02​
20.11​
18.97​
17.62​
16.74​
15.1​
12.95​
5/14/2020​
LZ_NORTHActual Load (with Losses)
314.16​
9.75​
8.41​
7.51​
6.94​
6.52​
6.22​
6.36​
7.12​
8.14​
9.24​
10.55​
12.45​
14.25​
15.76​
17.14​
18.68​
20.11​
21.35​
21.41​
20.08​
18.55​
17.76​
16.04​
13.81​
5/14/2020​
LZ_SOUTHForecast (without Losses)
40​
1.4​
1.2​
1.2​
1.1​
1.1​
1.1​
1.3​
1.3​
1.3​
1.3​
1.4​
1.6​
1.7​
1.8​
1.9​
2.1​
2.2​
2.3​
2.3​
2.2​
2.2​
2.2​
2​
1.7​
5/14/2020​
LZ_SOUTHForecast (with Losses)
42.8​
1.5​
1.3​
1.3​
1.2​
1.2​
1.2​
1.4​
1.4​
1.4​
1.4​
1.5​
1.7​
1.8​
1.9​
2.1​
2.2​
2.3​
2.5​
2.5​
2.4​
2.4​
2.3​
2.1​
1.8​
5/14/2020​
LZ_SOUTHActual Load (without Losses)
30.93​
1.06​
0.94​
0.85​
0.78​
0.73​
0.7​
0.7​
0.75​
0.78​
0.84​
0.95​
1.13​
1.35​
1.53​
1.66​
1.77​
1.92​
2.08​
2.04​
1.95​
1.88​
1.72​
1.52​
1.33​
5/14/2020​
LZ_SOUTHActual Load (with Losses)
33.31​
1.14​
1.01​
0.91​
0.84​
0.79​
0.76​
0.76​
0.82​
0.85​
0.92​
1.04​
1.22​
1.46​
1.65​
1.79​
1.91​
2.07​
2.23​
2.19​
2.08​
1.99​
1.84​
1.63​
1.43​
5/14/2020​
LZ_WESTForecast (without Losses)
60.2​
2.3​
2.2​
2.1​
2.1​
2.1​
2.2​
2.3​
2.4​
2.4​
2.5​
2.5​
2.6​
2.6​
2.7​
2.7​
2.8​
2.8​
2.8​
2.8​
2.8​
2.7​
2.7​
2.6​
2.4​
5/14/2020​
LZ_WESTForecast (with Losses)
64.5​
2.4​
2.3​
2.3​
2.3​
2.3​
2.3​
2.5​
2.6​
2.6​
2.6​
2.7​
2.8​
2.8​
2.9​
2.9​
3​
3​
3​
3​
3​
2.9​
2.9​
2.8​
2.6​
5/14/2020​
LZ_WESTActual Load (without Losses)
10.91​
0.52​
0.25​
0.22​
0.2​
0.18​
0.19​
0.21​
0.24​
0.28​
0.32​
0.35​
0.41​
0.47​
0.54​
0.63​
0.7​
0.72​
0.77​
0.73​
0.7​
0.64​
0.61​
0.55​
0.47​
5/14/2020​
LZ_WESTActual Load (with Losses)
11.69​
0.55​
0.27​
0.24​
0.21​
0.2​
0.21​
0.22​
0.26​
0.3​
0.35​
0.38​
0.45​
0.5​
0.58​
0.67​
0.75​
0.77​
0.82​
0.78​
0.74​
0.68​
0.65​
0.59​
0.51​

- Delete Column D "Total"
- Transpose the data, but row stack by date
- Enter "DATE" in cell A2 and "HE" in cell B2

LZ_HOUSTONLZ_HOUSTONLZ_HOUSTONLZ_HOUSTONLZ_NORTHLZ_NORTHLZ_NORTHLZ_NORTHLZ_SOUTHLZ_SOUTHLZ_SOUTHLZ_SOUTHLZ_WESTLZ_WESTLZ_WESTLZ_WEST
DATEHEForecast (without Losses)Forecast (with Losses)Actual Load (without Losses)Actual Load (with Losses)Forecast (without Losses)Forecast (with Losses)Actual Load (without Losses)Actual Load (with Losses)Forecast (without Losses)Forecast (with Losses)Actual Load (without Losses)Actual Load (with Losses)Forecast (without Losses)Forecast (with Losses)Actual Load (without Losses)Actual Load (with Losses)
5/13/2020​
0:00​
11.1​
11.9​
11.35​
11.96​
7.7​
8.2​
6.94​
7.26​
1.3​
1.4​
0.85​
0.89​
2.2​
2.4​
0.32​
0.33​
5/13/2020​
1:00​
9.8​
10.5​
10.15​
10.73​
6.6​
7​
5.98​
6.27​
1.2​
1.3​
0.74​
0.78​
2.2​
2.3​
0.27​
0.28​
5/13/2020​
2:00​
8.9​
9.6​
9.19​
9.75​
6​
6.5​
5.33​
5.61​
1.1​
1.2​
0.68​
0.72​
2.1​
2.3​
0.23​
0.24​
5/13/2020​
3:00​
8.2​
8.7​
8.58​
9.14​
5.7​
6.1​
4.97​
5.25​
1.1​
1.2​
0.59​
0.64​
2.1​
2.2​
0.21​
0.23​
5/13/2020​
4:00​
7.9​
8.5​
8.06​
8.56​
5.6​
6​
4.88​
5.14​
1.1​
1.1​
0.6​
0.64​
2.1​
2.2​
0.2​
0.21​
5/13/2020​
5:00​
8.4​
8.9​
7.65​
8.14​
6.2​
6.6​
4.85​
5.11​
1.1​
1.2​
0.6​
0.64​
2.2​
2.3​
0.2​
0.21​
5/13/2020​
6:00​
9.2​
9.8​
7.59​
8.07​
7.6​
8.1​
5.21​
5.5​
1.3​
1.4​
0.6​
0.64​
2.3​
2.5​
0.21​
0.22​
5/13/2020​
7:00​
8.9​
9.5​
8.06​
8.6​
8.5​
9.1​
6.03​
6.38​
1.3​
1.4​
0.7​
0.74​
2.4​
2.6​
0.25​
0.27​
5/13/2020​
8:00​
8.9​
9.5​
9.2​
9.88​
7.5​
8​
6.91​
7.37​
1.2​
1.3​
0.76​
0.82​
2.4​
2.6​
0.26​
0.28​
5/13/2020​
9:00​
9.5​
10.2​
10.71​
11.56​
7.3​
7.8​
7.59​
8.14​
1.3​
1.4​
0.81​
0.88​
2.4​
2.6​
0.28​
0.3​
5/13/2020​
10:00​
11.3​
12.1​
12.76​
13.73​
7.7​
8.3​
8.39​
8.97​
1.4​
1.5​
0.92​
1​
2.5​
2.7​
0.29​
0.32​
5/13/2020​
11:00​
13​
13.9​
15.21​
16.3​
8.5​
9.1​
8.97​
9.55​
1.5​
1.6​
1.06​
1.14​
2.5​
2.7​
0.32​
0.34​
5/13/2020​
12:00​
14.9​
16​
17.66​
18.82​
9.2​
9.9​
9.56​
10.13​
1.6​
1.7​
1.16​
1.24​
2.6​
2.8​
0.36​
0.39​
5/13/2020​
13:00​
16.8​
18​
19.33​
20.61​
10.2​
10.9​
10​
10.6​
1.7​
1.9​
1.27​
1.36​
2.6​
2.8​
0.41​
0.44​
5/13/2020​
14:00​
18.3​
19.5​
20.17​
21.62​
11.1​
11.9​
10.73​
11.44​
1.8​
2​
1.43​
1.55​
2.7​
2.8​
0.49​
0.52​
5/13/2020​
15:00​
19.7​
21.1​
20.81​
22.31​
12.2​
13.1​
11.87​
12.66​
2​
2.1​
1.52​
1.64​
2.7​
2.9​
0.56​
0.6​
5/13/2020​
16:00​
20.8​
22.3​
21.59​
23.16​
13.7​
14.6​
13.12​
14​
2.1​
2.3​
1.66​
1.79​
2.7​
2.9​
0.63​
0.67​
5/13/2020​
17:00​
21.4​
23​
21.8​
23.32​
14.7​
15.8​
14.84​
15.79​
2.2​
2.4​
1.84​
1.97​
2.7​
2.9​
0.68​
0.73​
5/13/2020​
18:00​
21.4​
22.9​
21.55​
23.12​
15.3​
16.4​
15.71​
16.76​
2.2​
2.4​
1.85​
2​
2.7​
2.9​
0.7​
0.75​
5/13/2020​
19:00​
19.9​
21.3​
20.21​
21.68​
14.9​
15.9​
15.32​
16.34​
2.2​
2.3​
1.8​
1.93​
2.7​
2.8​
0.62​
0.67​
5/13/2020​
20:00​
19.6​
21​
19.3​
20.72​
14.6​
15.7​
14.54​
15.52​
2.1​
2.3​
1.66​
1.79​
2.6​
2.8​
0.55​
0.59​
5/13/2020​
21:00​
18.8​
20.1​
18.23​
19.67​
14.7​
15.7​
14.04​
15.06​
2.1​
2.3​
1.59​
1.72​
2.6​
2.8​
0.53​
0.57​
5/13/2020​
22:00​
16.8​
18​
16.57​
17.87​
13.1​
14​
12.68​
13.6​
1.9​
2​
1.43​
1.55​
2.5​
2.7​
0.44​
0.48​
5/13/2020​
23:00​
14​
15​
14.38​
15.51​
10.8​
11.5​
10.88​
11.65​
1.6​
1.7​
1.22​
1.32​
2.4​
2.5​
0.37​
0.4​
5/13/2020​
0:00​
11.9​
12.8​
12.6​
13.52​
8.6​
9.2​
9.15​
9.75​
1.4​
1.5​
1.06​
1.14​
2.3​
2.4​
0.52​
0.55​
5/14/2020​
1:00​
10.5​
11.2​
11.21​
12.07​
7.4​
7.9​
7.87​
8.41​
1.2​
1.3​
0.94​
1.01​
2.2​
2.3​
0.25​
0.27​
5/14/2020​
2:00​
9.5​
10.2​
10.23​
11.04​
6.8​
7.3​
7.01​
7.51​
1.2​
1.3​
0.85​
0.91​
2.1​
2.3​
0.22​
0.24​
5/14/2020​
3:00​
8.8​
9.5​
9.52​
10.3​
6.4​
6.8​
6.47​
6.94​
1.1​
1.2​
0.78​
0.84​
2.1​
2.3​
0.2​
0.21​
5/14/2020​
4:00​
8.6​
9.2​
9.02​
9.77​
6.3​
6.7​
6.07​
6.52​
1.1​
1.2​
0.73​
0.79​
2.1​
2.3​
0.18​
0.2​
5/14/2020​
5:00​
8.9​
9.6​
8.65​
9.37​
6.9​
7.3​
5.79​
6.22​
1.1​
1.2​
0.7​
0.76​
2.2​
2.3​
0.19​
0.21​
5/14/2020​
6:00​
9.5​
10.2​
8.57​
9.28​
8.3​
8.8​
5.92​
6.36​
1.3​
1.4​
0.7​
0.76​
2.3​
2.5​
0.21​
0.22​
5/14/2020​
7:00​
9.2​
9.9​
9.2​
9.99​
9.1​
9.8​
6.6​
7.12​
1.3​
1.4​
0.75​
0.82​
2.4​
2.6​
0.24​
0.26​
5/14/2020​
8:00​
9.4​
10​
10.51​
11.41​
8.2​
8.8​
7.55​
8.14​
1.3​
1.4​
0.78​
0.85​
2.4​
2.6​
0.28​
0.3​
5/14/2020​
9:00​
9.8​
10.5​
12.47​
13.56​
8​
8.6​
8.55​
9.24​
1.3​
1.4​
0.84​
0.92​
2.5​
2.6​
0.32​
0.35​
5/14/2020​
10:00​
11.7​
12.5​
14.49​
15.66​
8.9​
9.5​
9.82​
10.55​
1.4​
1.5​
0.95​
1.04​
2.5​
2.7​
0.35​
0.38​
5/14/2020​
11:00​
13.3​
14.2​
16.67​
17.92​
10.1​
10.8​
11.65​
12.45​
1.6​
1.7​
1.13​
1.22​
2.6​
2.8​
0.41​
0.45​
5/14/2020​
12:00​
15.3​
16.4​
18.82​
20.2​
11.2​
12​
13.34​
14.25​
1.7​
1.8​
1.35​
1.46​
2.6​
2.8​
0.47​
0.5​
5/14/2020​
13:00​
17​
18.2​
20.73​
22.25​
12.3​
13.2​
14.76​
15.76​
1.8​
1.9​
1.53​
1.65​
2.7​
2.9​
0.54​
0.58​
5/14/2020​
14:00​
18.2​
19.5​
22.49​
24.09​
13.3​
14.2​
16.08​
17.14​
1.9​
2.1​
1.66​
1.79​
2.7​
2.9​
0.63​
0.67​
5/14/2020​
15:00​
19.5​
20.8​
24.03​
25.72​
14.3​
15.3​
17.55​
18.68​
2.1​
2.2​
1.77​
1.91​
2.8​
3​
0.7​
0.75​
5/14/2020​
16:00​
20.4​
21.8​
24.79​
26.5​
15.6​
16.7​
18.9​
20.11​
2.2​
2.3​
1.92​
2.07​
2.8​
3​
0.72​
0.77​
5/14/2020​
17:00​
21​
22.4​
25.31​
27.12​
16.4​
17.5​
20.02​
21.35​
2.3​
2.5​
2.08​
2.23​
2.8​
3​
0.77​
0.82​
5/14/2020​
18:00​
20.9​
22.4​
24.54​
26.26​
16.9​
18​
20.11​
21.41​
2.3​
2.5​
2.04​
2.19​
2.8​
3​
0.73​
0.78​
5/14/2020​
19:00​
20​
21.4​
22.53​
23.98​
16.4​
17.5​
18.97​
20.08​
2.2​
2.4​
1.95​
2.08​
2.8​
3​
0.7​
0.74​
5/14/2020​
20:00​
19.8​
21.2​
20.86​
22.08​
16​
17.1​
17.62​
18.55​
2.2​
2.4​
1.88​
1.99​
2.7​
2.9​
0.64​
0.68​
5/14/2020​
21:00​
19.2​
20.6​
19.39​
20.69​
16.1​
17.2​
16.74​
17.76​
2.2​
2.3​
1.72​
1.84​
2.7​
2.9​
0.61​
0.65​
5/14/2020​
22:00​
17.3​
18.5​
17.22​
18.4​
14.4​
15.4​
15.1​
16.04​
2​
2.1​
1.52​
1.63​
2.6​
2.8​
0.55​
0.59​
5/14/2020​
23:00​
14.7​
15.8​
14.59​
15.66​
12​
12.8​
12.95​
13.81​
1.7​
1.8​
1.33​
1.43​
2.4​
2.6​
0.47​
0.51​

- Delete columns R, Q, O, N, M, K, J, I, G, F, E, C

LZ_HOUSTONLZ_NORTHLZ_SOUTHLZ_WEST
DATEHEForecast (with Losses)Forecast (with Losses)Forecast (with Losses)Forecast (with Losses)
5/13/2020​
0:00​
11.9​
8.2​
1.4​
2.4​
5/13/2020​
1:00​
10.5​
7​
1.3​
2.3​
5/13/2020​
2:00​
9.6​
6.5​
1.2​
2.3​
5/13/2020​
3:00​
8.7​
6.1​
1.2​
2.2​
5/13/2020​
4:00​
8.5​
6​
1.1​
2.2​
5/13/2020​
5:00​
8.9​
6.6​
1.2​
2.3​
5/13/2020​
6:00​
9.8​
8.1​
1.4​
2.5​
5/13/2020​
7:00​
9.5​
9.1​
1.4​
2.6​
5/13/2020​
8:00​
9.5​
8​
1.3​
2.6​
5/13/2020​
9:00​
10.2​
7.8​
1.4​
2.6​
5/13/2020​
10:00​
12.1​
8.3​
1.5​
2.7​
5/13/2020​
11:00​
13.9​
9.1​
1.6​
2.7​
5/13/2020​
12:00​
16​
9.9​
1.7​
2.8​
5/13/2020​
13:00​
18​
10.9​
1.9​
2.8​
5/13/2020​
14:00​
19.5​
11.9​
2​
2.8​
5/13/2020​
15:00​
21.1​
13.1​
2.1​
2.9​
5/13/2020​
16:00​
22.3​
14.6​
2.3​
2.9​
5/13/2020​
17:00​
23​
15.8​
2.4​
2.9​
5/13/2020​
18:00​
22.9​
16.4​
2.4​
2.9​
5/13/2020​
19:00​
21.3​
15.9​
2.3​
2.8​
5/13/2020​
20:00​
21​
15.7​
2.3​
2.8​
5/13/2020​
21:00​
20.1​
15.7​
2.3​
2.8​
5/13/2020​
22:00​
18​
14​
2​
2.7​
5/13/2020​
23:00​
15​
11.5​
1.7​
2.5​
5/13/2020​
0:00​
12.8​
9.2​
1.5​
2.4​
5/14/2020​
1:00​
11.2​
7.9​
1.3​
2.3​
5/14/2020​
2:00​
10.2​
7.3​
1.3​
2.3​
5/14/2020​
3:00​
9.5​
6.8​
1.2​
2.3​
5/14/2020​
4:00​
9.2​
6.7​
1.2​
2.3​
5/14/2020​
5:00​
9.6​
7.3​
1.2​
2.3​
5/14/2020​
6:00​
10.2​
8.8​
1.4​
2.5​
5/14/2020​
7:00​
9.9​
9.8​
1.4​
2.6​
5/14/2020​
8:00​
10​
8.8​
1.4​
2.6​
5/14/2020​
9:00​
10.5​
8.6​
1.4​
2.6​
5/14/2020​
10:00​
12.5​
9.5​
1.5​
2.7​
5/14/2020​
11:00​
14.2​
10.8​
1.7​
2.8​
5/14/2020​
12:00​
16.4​
12​
1.8​
2.8​
5/14/2020​
13:00​
18.2​
13.2​
1.9​
2.9​
5/14/2020​
14:00​
19.5​
14.2​
2.1​
2.9​
5/14/2020​
15:00​
20.8​
15.3​
2.2​
3​
5/14/2020​
16:00​
21.8​
16.7​
2.3​
3​
5/14/2020​
17:00​
22.4​
17.5​
2.5​
3​
5/14/2020​
18:00​
22.4​
18​
2.5​
3​
5/14/2020​
19:00​
21.4​
17.5​
2.4​
3​
5/14/2020​
20:00​
21.2​
17.1​
2.4​
2.9​
5/14/2020​
21:00​
20.6​
17.2​
2.3​
2.9​
5/14/2020​
22:00​
18.5​
15.4​
2.1​
2.8​
5/14/2020​
23:00​
15.8​
12.8​
1.8​
2.6​

- Copy Cells 'C1:G' and Paste in C2
- Delete empty row

DATEHELZ_HOUSTONLZ_NORTHLZ_SOUTHLZ_WEST
5/13/2020​
0:00​
11.9​
8.2​
1.4​
2.4​
5/13/2020​
1:00​
10.5​
7​
1.3​
2.3​
5/13/2020​
2:00​
9.6​
6.5​
1.2​
2.3​
5/13/2020​
3:00​
8.7​
6.1​
1.2​
2.2​
5/13/2020​
4:00​
8.5​
6​
1.1​
2.2​
5/13/2020​
5:00​
8.9​
6.6​
1.2​
2.3​
5/13/2020​
6:00​
9.8​
8.1​
1.4​
2.5​
5/13/2020​
7:00​
9.5​
9.1​
1.4​
2.6​
5/13/2020​
8:00​
9.5​
8​
1.3​
2.6​
5/13/2020​
9:00​
10.2​
7.8​
1.4​
2.6​
5/13/2020​
10:00​
12.1​
8.3​
1.5​
2.7​
5/13/2020​
11:00​
13.9​
9.1​
1.6​
2.7​
5/13/2020​
12:00​
16​
9.9​
1.7​
2.8​
5/13/2020​
13:00​
18​
10.9​
1.9​
2.8​
5/13/2020​
14:00​
19.5​
11.9​
2​
2.8​
5/13/2020​
15:00​
21.1​
13.1​
2.1​
2.9​
5/13/2020​
16:00​
22.3​
14.6​
2.3​
2.9​
5/13/2020​
17:00​
23​
15.8​
2.4​
2.9​
5/13/2020​
18:00​
22.9​
16.4​
2.4​
2.9​
5/13/2020​
19:00​
21.3​
15.9​
2.3​
2.8​
5/13/2020​
20:00​
21​
15.7​
2.3​
2.8​
5/13/2020​
21:00​
20.1​
15.7​
2.3​
2.8​
5/13/2020​
22:00​
18​
14​
2​
2.7​
5/13/2020​
23:00​
15​
11.5​
1.7​
2.5​
5/13/2020​
0:00​
12.8​
9.2​
1.5​
2.4​
5/14/2020​
1:00​
11.2​
7.9​
1.3​
2.3​
5/14/2020​
2:00​
10.2​
7.3​
1.3​
2.3​
5/14/2020​
3:00​
9.5​
6.8​
1.2​
2.3​
5/14/2020​
4:00​
9.2​
6.7​
1.2​
2.3​
5/14/2020​
5:00​
9.6​
7.3​
1.2​
2.3​
5/14/2020​
6:00​
10.2​
8.8​
1.4​
2.5​
5/14/2020​
7:00​
9.9​
9.8​
1.4​
2.6​
5/14/2020​
8:00​
10​
8.8​
1.4​
2.6​
5/14/2020​
9:00​
10.5​
8.6​
1.4​
2.6​
5/14/2020​
10:00​
12.5​
9.5​
1.5​
2.7​
5/14/2020​
11:00​
14.2​
10.8​
1.7​
2.8​
5/14/2020​
12:00​
16.4​
12​
1.8​
2.8​
5/14/2020​
13:00​
18.2​
13.2​
1.9​
2.9​
5/14/2020​
14:00​
19.5​
14.2​
2.1​
2.9​
5/14/2020​
15:00​
20.8​
15.3​
2.2​
3​
5/14/2020​
16:00​
21.8​
16.7​
2.3​
3​
5/14/2020​
17:00​
22.4​
17.5​
2.5​
3​
5/14/2020​
18:00​
22.4​
18​
2.5​
3​
5/14/2020​
19:00​
21.4​
17.5​
2.4​
3​
5/14/2020​
20:00​
21.2​
17.1​
2.4​
2.9​
5/14/2020​
21:00​
20.6​
17.2​
2.3​
2.9​
5/14/2020​
22:00​
18.5​
15.4​
2.1​
2.8​
5/14/2020​
23:00​
15.8​
12.8​
1.8​
2.6​

On a side note, I tried achieving the same results using a pivot table, but I am not able to get the Hour Ending (i.e. 00:00, 01:00, etc) rows to nest under the date rows, which are nested under the Month rows.

Thanks in advance for all the help!

 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
is that what you want?

with Power Query aka Get&Transform

DateHELZ_HOUSTONLZ_NORTHLZ_SOUTHLZ_WEST
13/05/202000:0023.8615.462.292.73
13/05/202001:0021.2313.272.082.58
13/05/202002:0019.3512.111.922.54
13/05/202003:0017.8411.351.842.43
13/05/202004:0017.0611.141.742.41
13/05/202005:0017.0411.711.842.51
13/05/202006:0017.8713.62.042.72
13/05/202007:0018.115.482.142.87
13/05/202008:0019.3815.372.122.88
13/05/202009:0021.7615.942.282.9
13/05/202010:0025.8317.272.53.02
13/05/202011:0030.218.652.743.04
13/05/202012:0034.8220.032.943.19
13/05/202013:0038.6121.53.263.24
13/05/202014:0041.1223.343.553.32
13/05/202015:0043.4125.763.743.5
13/05/202016:0045.4628.64.093.57
13/05/202017:0046.3231.594.373.63
13/05/202018:0046.0233.164.43.65
13/05/202019:0042.9832.244.233.47
13/05/202020:0041.7231.224.093.39
13/05/202021:0039.7730.764.023.37
13/05/202022:0035.8727.63.553.18
13/05/202023:0030.5123.153.022.9
14/05/202000:0026.3218.952.642.95
14/05/202001:0023.2716.312.312.57
14/05/202002:0021.2414.812.212.54
14/05/202003:0019.813.742.042.51
14/05/202004:0018.9713.221.992.5
14/05/202005:0018.9713.521.962.51
14/05/202006:0019.4815.162.162.72
14/05/202007:0019.8916.922.222.86
14/05/202008:0021.4116.942.252.9
14/05/202009:0024.0617.842.322.95
14/05/202010:0028.1620.052.543.08
14/05/202011:0032.1223.252.923.25
14/05/202012:0036.626.253.263.3
14/05/202013:0040.4528.963.553.48
14/05/202014:0043.5931.343.893.57
14/05/202015:0046.5233.984.113.75
14/05/202016:0048.336.814.373.77
14/05/202017:0049.5238.854.733.82
14/05/202018:0048.6639.414.693.78
14/05/202019:0045.3837.584.483.74
14/05/202020:0043.2835.654.393.58
14/05/202021:0041.2934.964.143.55
14/05/202022:0036.931.443.733.39
14/05/202023:0031.4626.613.233.11
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I forgot to post M so here is:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeDateLocale = Table.TransformColumnTypes(Source, {{"OperatingDate", type date}}, "en-US"),
    RC = Table.RemoveColumns(TypeDateLocale,{"Total"}),
    Filter = Table.SelectRows(RC, each ([DataType] = "Actual Load (with Losses)" or [DataType] = "Forecast (with Losses)")),
    UOC = Table.UnpivotOtherColumns(Filter, {"OperatingDate", "Zone", "DataType"}, "HE", "Value"),
    TypeTime = Table.TransformColumnTypes(UOC,{{"HE", type time}}),
    RC1 = Table.RemoveColumns(TypeTime,{"DataType"}),
    Pivot = Table.Pivot(RC1, List.Distinct(RC1[Zone]), "Zone", "Value", List.Sum),
    Ren = Table.RenameColumns(Pivot,{{"OperatingDate", "Date"}})
in
    Ren
 

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Thanks @sandy666 ! I have another question, what would the powerquery code look like if I wanted to take multiple rows of data and have them line up against each other in a single row? For example, starting with this:

DATEZONEHE1HE2HE3HE4HE5HE6HE7HE8HE9HE10HE11HE12HE13HE14HE15HE16HE17HE18HE19HE20HE21HE22HE23HE24
8/18/2020​
Location A11.7110.189.339.549.109.119.379.389.6610.7212.1614.1817.7620.4620.7322.3426.1523.2122.4321.4320.6220.3519.1516.68
8/19/2020​
Location A7.916.816.135.565.606.317.678.108.468.869.059.899.1610.0910.7011.8415.8818.2917.6515.5615.3215.1013.9111.69

with the end output of this in mind:

8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/18/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
8/19/2020​
ZONEHE1HE2HE3HE4HE5HE6HE7HE8HE9HE10HE11HE12HE13HE14HE15HE16HE17HE18HE19HE20HE21HE22HE23HE24HE1HE2HE3HE4HE5HE6HE7HE8HE9HE10HE11HE12HE13HE14HE15HE16HE17HE18HE19HE20HE21HE22HE23HE24
Location A11.7110.189.339.549.109.119.379.389.6610.7212.1614.1817.7620.4620.7322.3426.1523.2122.4321.4320.6220.3519.1516.687.916.816.135.565.606.317.678.108.468.869.059.899.1610.0910.7011.8415.8818.2917.6515.5615.3215.1013.9111.69
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,337
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top