Structuring data correctly to use in pivottable

[muhleebbin]

Hi,

Could someone please assist me in structuring the following data correctly so that it's usable in a pivot table?

I currently have a sheet where we input data and with a button it takes all relevant data and puts it into another sheet "Data".

NTE Tracking v3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	COR-PCO Number:
2
3	Change Order:									View Records
4
5	RFI/Bulletin/ROM:										1	of	3
6
7	Status:
8
9	Item #	Description	Material Unit Price	Shop Labor	Site Labor	Total
10	001	Test 1	-	-	-	-
11	002	Test 2	-	-	-	-
12	003	Test 3	-	-	-	-
13	004	Test 4	-	-	-	-
14	005	Test 5	-	-	-	-
15			-	-	-	-
27	Site, Extra 01 Subtotal:		-	-	-	-
Input

Cell Formulas
Range	Formula
M5	M5	=COUNTA(Data!A:A)
F10:F15,F27	F10	=SUM(C10:E10)
C27:E27	C27	=SUM(C10:C26)

Cells with Data Validation
Cell	Allow	Criteria
K5	Whole number	between 1 and M5
D7:F7	List	=Lists!$Y$1:$Y$3

The Data sheet looks like this:

NTE Tracking v3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
1			COR-PCO Number	Change Order	RFI/Bulletin/ROM	Status	MU 10	MU 11	MU 12	MU 13	MU 14	MU 15	MU 16	MU 17	MU 18	MU 19	MU 20	MU 21	MU 22	MU 23	MU 24	MU 25	MU 26	SL 10	SL 11	SL 12	SL 13	SL 14	SL 15	SL 16	SL 17	SL 18	SL 19	SL 20	SL 21	SL 22	SL 23	SL 24	SL 25	SL 26	S 10	S 11	S 12	S 13	S 14	S 15	S 16	S 17	S 18	S 19	S 20	S 21	S 22	S 23	S 24	S 25	S 26
2	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	10	0	0	40	0	0	0	0	0	0	0	0	0	0	0	0	0	0	20	0	50	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	30	60	0	0	0	0	0	0	0	0	0	0	0	0	0
3	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	100	0	0	400	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	0	500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	300	600	0	0	0	0	0	0	0	0	0	0	0	0	0
4	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	1000	0	0	4000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2000	0	5000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3000	6000	0	0	0	0	0	0	0	0	0	0	0	0	0
Data

Is there a way to convert the data to look like this?

NTE Tracking v3.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	User	COR-PCO Number	Change Order	RFI/Bulletin/ROM	Status	Column	Row	Amount
2	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	10	10
3	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	11	0
4	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	12	0
5	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	13	40
6	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	14	0
7	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	15	0
8	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	16	0
9	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	17	0
10	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	18	0
11	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	19	0
12	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	20	0
13	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	21	0
14	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	22	0
15	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	23	0
16	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	24	0
17	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	25	0
18	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	26	0
19	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	10	0
20	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	11	20
21	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	12	0
22	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	13	50
23	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	14	0
24	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	15	0
25	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	16	0
26	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	17	0
27	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	18	0
28	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	19	0
29	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	20	0
30	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	21	0
31	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	22	0
32	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	23	0
33	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	24	0
34	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	25	0
35	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	26	0
36	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	10	0
37	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	11	0
38	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	12	30
39	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	13	60
40	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	14	0
41	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	15	0
42	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	16	0
43	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	17	0
44	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	18	0
45	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	19	0
46	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	20	0
47	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	21	0
48	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	22	0
49	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	23	0
50	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	24	0
51	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	25	0
52	09/17/2020 08:08:43	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	26	0
53	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	10	100
54	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	11	0
55	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	12	0
56	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	13	400
57	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	14	0
58	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	15	0
59	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	16	0
60	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	17	0
61	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	18	0
62	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	19	0
63	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	20	0
64	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	21	0
65	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	22	0
66	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	23	0
67	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	24	0
68	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	25	0
69	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	26	0
70	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	10	0
71	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	11	200
72	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	12	0
73	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	13	500
74	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	14	0
75	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	15	0
76	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	16	0
77	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	17	0
78	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	18	0
79	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	19	0
80	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	20	0
81	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	21	0
82	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	22	0
83	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	23	0
84	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	24	0
85	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	25	0
86	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	26	0
87	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	10	0
88	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	11	0
89	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	12	300
90	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	13	600
91	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	14	0
92	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	15	0
93	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	16	0
94	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	17	0
95	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	18	0
96	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	19	0
97	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	20	0
98	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	21	0
99	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	22	0
100	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	23	0
101	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	24	0
102	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	25	0
103	09/17/2020 08:09:35	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	26	0
104	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	10	1000
105	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	11	0
106	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	12	0
107	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	13	4000
108	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	14	0
109	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	15	0
110	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	16	0
111	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	17	0
112	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	18	0
113	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	19	0
114	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	20	0
115	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	21	0
116	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	22	0
117	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	23	0
118	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	24	0
119	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	25	0
120	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	26	0
121	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	10	0
122	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	11	2000
123	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	12	0
124	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	13	5000
125	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	14	0
126	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	15	0
127	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	16	0
128	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	17	0
129	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	18	0
130	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	19	0
131	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	20	0
132	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	21	0
133	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	22	0
134	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	23	0
135	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	24	0
136	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	25	0
137	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	26	0
138	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	10	0
139	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	11	0
140	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	12	3000
141	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	13	6000
142	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	14	0
143	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	15	0
144	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	16	0
145	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	17	0
146	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	18	0
147	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	19	0
148	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	20	0
149	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	21	0
150	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	22	0
151	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	23	0
152	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	24	0
153	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	25	0
154	09/17/2020 08:10:07	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	26	0
Data3

As you could imagine, the pivot table using the first data is disturbingly disgusting whereas the second one would give me a nice clean pivot that looks very much like the original data. Is it even necessary to have the code that writes the first data as it is currently or could that be rewritten to just output it in this second more ideal format?

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "D1,D3,D5,D7,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,E10,E11,E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22,E23,E24,E25,E26"

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Data")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Range("D1:F1").ClearContents
    Range("D3:F3").ClearContents
    Range("D5:F5").ClearContents
    Range("D7:F7").ClearContents
    Range("C10") = "0"
    Range("C11") = "0"
    Range("C12") = "0"
    Range("C13") = "0"
    Range("C14") = "0"
    Range("C15") = "0"
    Range("C16") = "0"
    Range("C17") = "0"
    Range("C18") = "0"
    Range("C19") = "0"
    Range("C20") = "0"
    Range("C21") = "0"
    Range("C22") = "0"
    Range("C23") = "0"
    Range("C24") = "0"
    Range("C25") = "0"
    Range("C26") = "0"
    Range("D10") = "0"
    Range("D11") = "0"
    Range("D12") = "0"
    Range("D13") = "0"
    Range("D14") = "0"
    Range("D15") = "0"
    Range("D16") = "0"
    Range("D17") = "0"
    Range("D18") = "0"
    Range("D19") = "0"
    Range("D20") = "0"
    Range("D21") = "0"
    Range("D22") = "0"
    Range("D23") = "0"
    Range("D24") = "0"
    Range("D25") = "0"
    Range("D26") = "0"
    Range("E10") = "0"
    Range("E11") = "0"
    Range("E12") = "0"
    Range("E13") = "0"
    Range("E14") = "0"
    Range("E15") = "0"
    Range("E16") = "0"
    Range("E17") = "0"
    Range("E18") = "0"
    Range("E19") = "0"
    Range("E20") = "0"
    Range("E21") = "0"
    Range("E22") = "0"
    Range("E23") = "0"
    Range("E24") = "0"
    Range("E25") = "0"
    Range("E26") = "0"

End Sub

 

[sandy666]

With Power Query aka Get&Transform

Date	User	COR-PCO Number	Change Order	RFI/Bulletin/ROM	Status	MU 10	MU 11	MU 12	MU 13	MU 14	MU 15	MU 16	MU 17	MU 18	MU 19	MU 20	MU 21	MU 22	MU 23	MU 24	MU 25	MU 26	SL 10	SL 11	SL 12	SL 13	SL 14	SL 15	SL 16	SL 17	SL 18	SL 19	SL 20	SL 21	SL 22	SL 23	SL 24	SL 25	SL 26	S 10	S 11	S 12	S 13	S 14	S 15	S 16	S 17	S 18	S 19	S 20	S 21	S 22	S 23	S 24	S 25	S 26
44091.33939	Test	Test 1	Test CO 1	Test RFI 1	Pending	10	0	0	40	0	0	0	0	0	0	0	0	0	0	0	0	0	0	20	0	50	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	30	60	0	0	0	0	0	0	0	0	0	0	0	0	0
44091.33999	Test	Test 2	Test CO 2	Test RFI 2	Proposed	100	0	0	400	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	0	500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	300	600	0	0	0	0	0	0	0	0	0	0	0	0	0
44091.34036	Test	Test 3	Test CO 3	Test RFI 3	Approved	1000	0	0	4000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2000	0	5000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3000	6000	0	0	0	0	0	0	0	0	0	0	0	0	0

Date	User	COR-PCO Number	Change Order	RFI/Bulletin/ROM	Status	Column	Row	Amount
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	10	10
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	11	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	12	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	13	40
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	14	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	15	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	16	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	17	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	18	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	19	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	20	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	21	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	22	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	23	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	24	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	25	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	MU	26	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	10	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	11	20
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	12	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	13	50
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	14	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	15	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	16	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	17	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	18	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	19	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	20	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	21	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	22	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	23	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	24	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	25	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	SL	26	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	10	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	11	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	12	30
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	13	60
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	14	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	15	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	16	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	17	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	18	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	19	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	20	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	21	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	22	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	23	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	24	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	25	0
17/09/2020 08:08	Test	Test 1	Test CO 1	Test RFI 1	Pending	S	26	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	10	100
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	11	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	12	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	13	400
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	14	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	15	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	16	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	17	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	18	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	19	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	20	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	21	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	22	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	23	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	24	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	25	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	MU	26	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	10	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	11	200
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	12	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	13	500
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	14	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	15	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	16	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	17	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	18	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	19	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	20	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	21	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	22	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	23	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	24	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	25	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	SL	26	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	10	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	11	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	12	300
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	13	600
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	14	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	15	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	16	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	17	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	18	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	19	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	20	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	21	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	22	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	23	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	24	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	25	0
17/09/2020 08:09	Test	Test 2	Test CO 2	Test RFI 2	Proposed	S	26	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	10	1000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	11	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	12	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	13	4000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	14	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	15	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	16	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	17	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	18	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	19	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	20	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	21	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	22	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	23	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	24	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	25	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	MU	26	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	10	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	11	2000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	12	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	13	5000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	14	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	15	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	16	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	17	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	18	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	19	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	20	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	21	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	22	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	23	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	24	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	25	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	SL	26	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	10	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	11	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	12	3000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	13	6000
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	14	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	15	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	16	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	17	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	18	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	19	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	20	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	21	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	22	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	23	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	24	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	25	0
17/09/2020 08:10	Test	Test 3	Test CO 3	Test RFI 3	Approved	S	26	0

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Date", "User", "COR-PCO Number", "Change Order", "RFI/Bulletin/ROM", "Status"}, "Attribute", "Value"),
    SC = Table.SplitColumn(UOC, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    Ren = Table.RenameColumns(SC,{{"Attribute.1", "Column"}, {"Attribute.2", "Row"}, {"Value", "Amount"}}),
    Type = Table.TransformColumnTypes(Ren,{{"Date", type datetime}, {"User", type text}, {"COR-PCO Number", type text}, {"Change Order", type text}, {"RFI/Bulletin/ROM", type text}, {"Status", type text}, {"Column", type text}, {"Row", Int64.Type}, {"Amount", Int64.Type}})
in
    Type

 

[muhleebbin]

Thank you very much Sandy! That worked very nicely....i'll be honest, I had no idea how to use Power Query let alone input that code so I had to teach myself a few things

 

[sandy666]

You are welcome
You will see this is a VERY useful tool for small and big things

 

[muhleebbin]

I can see that! It looks like you use it quite extensively, could you recommend a book or site where a novice could possibly learn more?

Thanks again and have a great weekend!

 

[sandy666]

the basics, but not only

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

The book you can buy here

only watch youtube video if you have lost all hope of a solution

 

[muhleebbin]

Thank you!

 

[sandy666]

you are welcome and thanks for the feedback
it's nice to see if someone appreciate your work so hit Like in the posts which have helped you
Have a nice day

 

[muhleebbin]

Ahhh didn't even notice that!

I often times wrote out my appreciation but will definitely hit the in the future

 

[sandy666]

will be nice to see thanks and Like