Max Value and Sum from a data set that appears to be duplicate: VBA

[shockey8oz]

Good afternoon-

I am attempting to find the max value from two data sets which appear to be duplicates. The data comes from two separate workbooks that I have bought into a combined data workbook. I have used a pivot table on the data; however, I am not getting my ideal results.

In the example, there are two data sets from the state of Iowa. One is from the East (Row 2-Row 16) and one from the West (Row 17-Row 36). The report does not differentiate between the two. The data sets will not always have the same number of data points. The report has a state, date, and hour of when a widget was in use. I need the max number of widget A and widget B in use during the duration of the event for each data set and sum that together.

In the example, Widget A would be 22 using the data in row 3 (4 widgets) & 18 (18 widgets). Widget B would be 10 using the data in row 14 (3 widgets) and row 33 (7 widgets).

I think the best solution would the best solution be to append column A with a differentiator? (IA - E, IA - W) Then, pivot table, then max value, then sum.

What is the best way to do that?

eot-summary (9).csv
	A	B	C	D	E
1	State	Date	Hour	Widget A	Widget B
2	IA	12/16/2021	10	3	0
3	IA	12/16/2021	9	4	0
4	IA	12/16/2021	8	2	0
5	IA	12/16/2021	7	2	0
6	IA	12/16/2021	6	2	0
7	IA	12/16/2021	5	2	0
8	IA	12/16/2021	4	2	0
9	IA	12/16/2021	3	2	0
10	IA	12/16/2021	2	2	0
11	IA	12/16/2021	1	2	2
12	IA	12/16/2021	0	2	2
13	IA	12/15/2021	23	2	2
14	IA	12/15/2021	22	1	3
15	IA	12/15/2021	21	0	3
16	IA	12/15/2021	20	0	1
17	IA	12/16/2021	10	18	1
18	IA	12/16/2021	9	18	1
19	IA	12/16/2021	8	18	3
20	IA	12/16/2021	7	17	3
21	IA	12/16/2021	6	15	3
22	IA	12/16/2021	5	15	3
23	IA	12/16/2021	4	15	3
24	IA	12/16/2021	3	15	3
25	IA	12/16/2021	2	13	3
26	IA	12/16/2021	1	12	2
27	IA	12/16/2021	0	12	2
28	IA	12/15/2021	23	10	2
29	IA	12/15/2021	22	8	1
30	IA	12/15/2021	21	5	5
31	IA	12/15/2021	20	2	6
32	IA	12/15/2021	19	1	6
33	IA	12/15/2021	18	0	7
34	IA	12/15/2021	17	0	5
35	IA	12/15/2021	16	0	3
36	IA	12/15/2021	15	0	2
Sheet145