Formatting and Table Change

[Pestomania]

Hello, I am trying to identify how to take one table and turn it into a different design with a different format. I have about 300 lines of programs that I need to take from the "top" table and turn it into the bottom table.

Thoughts?

Prestons Playground for Modeling.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Program	Process 1	Process 2	Process 3	Process 4	Process 5	Process 6	Process 7	Process 8	Process 9	Process 10	Process 11	Process 12	Process 13	Process 14	Process 15	Process 16	Total
2	Program 1	2	5	8	0	20	27	8	6	30	9	9	19	7	10	27	6	193
3	Program 2	13	8	26	17	7	11	20	7	25	30	28	27	14	2	24	8	267
4	Program 3	3	25	0	14	0	4	10	21	16	3	4	0	24	13	4	1	142
5	Program 4	28	18	11	28	6	5	0	21	0	8	27	27	15	29	14	5	242
6	Program 5	1	16	30	25	9	2	27	20	28	3	9	16	19	9	22	8	244
7	Program 6	26	17	6	24	10	3	4	0	0	0	9	26	19	9	0	2	155
8	Program 7	10	13	3	3	24	0	2	1	21	27	15	22	26	27	22	21	237
9	Program 8	1	18	0	0	20	2	10	17	1	12	11	17	12	13	29	3	166
10	Program 9	15	24	0	23	11	21	20	7	10	24	1	16	10	2	27	20	231
11	Program 10	17	20	21	0	17	0	0	11	27	22	7	18	22	20	16	14	232
12	Program 11	26	0	14	0	1	14	10	0	22	23	7	13	6	22	9	5	172
13	Program 12	23	16	14	21	18	0	19	2	13	0	15	11	0	27	5	29	213
14	Program 13	13	18	13	11	14	0	24	9	29	0	4	12	20	0	22	10	199
15	Program 14	28	25	2	14	9	3	9	18	29	13	28	19	5	2	3	20	227
16
17							How to only pull data from the row if the value is greater than 0, then add to a different table, transposed. Make the Program Name equal to column A and put the applicable process name in column D.
18
19
20	Program Name	Step Duration	Days from Start	Process Name
21	Program 1	2	193	Process 1
22	Program 1	5	191	Process 2
23	Program 1	8	186	Process 3
24	Program 1	20	178	Process 5
25	Program 1	27	158	Process 6
26	Program 1	8	131	Process 7
27	Program 1	6	123	Process 8
28	Program 1	30	117	Process 9
29	Program 1	9	87	Process 10
30	Program 1	9	78	Process 11
31	Program 1	19	69	Process 12
32	Program 1	7	50	Process 13
33	Program 1	10	43	Process 14
34	Program 1	27	33	Process 15
35	Program 1	6	6	Process 16
36	Program 2	13	267	Process 1
37	Program 2	8	254	Process 2
38	Program 2	26	246	Process 3
39	Program 2	17	220	Process 4
40	Program 2	7	203	Process 5
41	Program 2	11	196	Process 6
42	Program 2	20	185	Process 7
43	Program 2	7	165	Process 8
44	Program 2	25	158	Process 9
45	Program 2	30	133	Process 10
46	Program 2	28	103	Process 11
47	Program 2	27	75	Process 12
48	Program 2	14	48	Process 13
49	Program 2	2	34	Process 14
50	Program 2	24	32	Process 15
51	Program 2	8	8	Process 16
Sheet6

Cell Formulas
Range	Formula
R2:R15	R2	=SUM(Table6[@[Process 1]:[Process 16]])
C21:C35	C21	=SUM(B21:$B$35)
C36:C51	C36	=SUM(B36:$B$51)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:Q15	Cell Value	=0	text	NO

 

[igold]

Assuming that you can make (or it already is) Column R part of your table, and I did not do anything with your Headers in the results area, then this code produced these results... Using "Sheet6" and "Table6". The code worked through Program 14, but when I tried to enter the entire results range, XL2BB told me that the Max number of cells was 3,000, so I stopped copying at Program 10.

Sub TransPoseTable()
    
    Dim i As Long, x As Long, r As Long, lr As Long, lRow As Long, ld As Long, j As Long
    Dim tbl As ListObject: Set tbl = Worksheets("Sheet6").ListObjects("Table6")
    Dim arr, arrL, hdr, arr2, col, colx, arrLD
    Dim tr As Long
     
    hdr = tbl.HeaderRowRange
    r = 1: tr = 1
     
    Do Until tr > tbl.ListRows.Count
        j = 1: i = 1
        arrLD = tbl.ListRows(tr).Range

        'Clean ListRow
        ReDim arrL(1 To 1, 1 To UBound(arrLD, 2))
        For ld = 1 To UBound(arrLD, 2)
            If Not arrLD(1, ld) = 0 Then
                arrL(1, j) = arrLD(1, ld)
                j = j + 1
            End If
        Next

        ReDim Preserve arrL(1 To 1, 1 To j - 1)

        lr = tbl.ListRows(r).Range.Cells.Count
        ReDim col(1 To j - 1, 1 To 4)

        For x = 1 To j - 3
            col(i, 1) = arrL(1, 1)
            col(i, 2) = arrL(1, 1 + i)
            If Not i = 1 Then
                col(i, 3) = col(i - 1, 3) - col(x - r, 2)
            End If
            col(i, 4) = hdr(1, x + 1)
            If i = 1 Then
                col(i, 3) = arrL(1, UBound(arrL, 2))
            End If
            i = i + 1
        Next
        
        lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Range("A" & lRow).Resize(UBound(col), 4) = col
        tr = tr + 1
        If tr > tbl.ListRows.Count Then Exit Sub
        Loop
    
End Sub

Table to new Table.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Program	Process 1	Process 2	Process 3	Process 4	Process 5	Process 6	Process 7	Process 8	Process 9	Process 10	Process 11	Process 12	Process 13	Process 14	Process 15	Process 16	Total
2	Program 1	2	5	8	0	20	27	8	6	30	9	9	19	7	10	27	6	193
3	Program 2	13	8	26	17	7	11	20	7	25	30	28	27	14	2	24	8	267
4	Program 3	3	25	0	14	0	4	10	21	16	3	4	0	24	13	4	1	142
5	Program 4	28	18	11	28	6	5	0	21	0	8	27	27	15	29	14	5	242
6	Program 5	1	16	30	25	9	2	27	20	28	3	9	16	19	9	22	8	244
7	Program 6	26	17	6	24	10	3	4	0	0	0	9	26	19	9	0	2	155
8	Program 7	10	13	3	3	24	0	2	1	21	27	15	22	26	27	22	21	237
9	Program 8	1	18	0	0	20	2	10	17	1	12	11	17	12	13	29	3	166
10	Program 9	15	24	0	23	11	21	20	7	10	24	1	16	10	2	27	20	231
11	Program 10	17	20	21	0	17	0	0	11	27	22	7	18	22	20	16	14	232
12	Program 11	26	0	14	0	1	14	10	0	22	23	7	13	6	22	9	5	172
13	Program 12	23	16	14	21	18	0	19	2	13	0	15	11	0	27	5	29	213
14	Program 13	13	18	13	11	14	0	24	9	29	0	4	12	20	0	22	10	199
15	Program 14	28	25	2	14	9	3	9	18	29	13	28	19	5	2	3	20	227
16
17
18
19
20	Program Name	Step Duration	Days from Start	Process Name
21	Program 1	2	193	Process 1
22	Program 1	5	191	Process 2
23	Program 1	8	186	Process 3
24	Program 1	20	178	Process 4
25	Program 1	27	158	Process 5
26	Program 1	8	131	Process 6
27	Program 1	6	123	Process 7
28	Program 1	30	117	Process 8
29	Program 1	9	87	Process 9
30	Program 1	9	78	Process 10
31	Program 1	19	69	Process 11
32	Program 1	7	50	Process 12
33	Program 1	10	43	Process 13
34	Program 1	27	33	Process 14
35	Program 1	6	6	Process 15
36	Program 2	13	267	Process 1
37	Program 2	8	254	Process 2
38	Program 2	26	246	Process 3
39	Program 2	17	220	Process 4
40	Program 2	7	203	Process 5
41	Program 2	11	196	Process 6
42	Program 2	20	185	Process 7
43	Program 2	7	165	Process 8
44	Program 2	25	158	Process 9
45	Program 2	30	133	Process 10
46	Program 2	28	103	Process 11
47	Program 2	27	75	Process 12
48	Program 2	14	48	Process 13
49	Program 2	2	34	Process 14
50	Program 2	24	32	Process 15
51	Program 2	8	8	Process 16
52	Program 3	3	142	Process 1
53	Program 3	25	139	Process 2
54	Program 3	14	114	Process 3
55	Program 3	4	100	Process 4
56	Program 3	10	96	Process 5
57	Program 3	21	86	Process 6
58	Program 3	16	65	Process 7
59	Program 3	3	49	Process 8
60	Program 3	4	46	Process 9
61	Program 3	24	42	Process 10
62	Program 3	13	18	Process 11
63	Program 3	4	5	Process 12
64	Program 3	1	1	Process 13
65	Program 4	28	242	Process 1
66	Program 4	18	214	Process 2
67	Program 4	11	196	Process 3
68	Program 4	28	185	Process 4
69	Program 4	6	157	Process 5
70	Program 4	5	151	Process 6
71	Program 4	21	146	Process 7
72	Program 4	8	125	Process 8
73	Program 4	27	117	Process 9
74	Program 4	27	90	Process 10
75	Program 4	15	63	Process 11
76	Program 4	29	48	Process 12
77	Program 4	14	19	Process 13
78	Program 4	5	5	Process 14
79	Program 5	1	244	Process 1
80	Program 5	16	243	Process 2
81	Program 5	30	227	Process 3
82	Program 5	25	197	Process 4
83	Program 5	9	172	Process 5
84	Program 5	2	163	Process 6
85	Program 5	27	161	Process 7
86	Program 5	20	134	Process 8
87	Program 5	28	114	Process 9
88	Program 5	3	86	Process 10
89	Program 5	9	83	Process 11
90	Program 5	16	74	Process 12
91	Program 5	19	58	Process 13
92	Program 5	9	39	Process 14
93	Program 5	22	30	Process 15
94	Program 5	8	8	Process 16
95	Program 6	26	155	Process 1
96	Program 6	17	129	Process 2
97	Program 6	6	112	Process 3
98	Program 6	24	106	Process 4
99	Program 6	10	82	Process 5
100	Program 6	3	72	Process 6
101	Program 6	4	69	Process 7
102	Program 6	9	65	Process 8
103	Program 6	26	56	Process 9
104	Program 6	19	30	Process 10
105	Program 6	9	11	Process 11
106	Program 6	2	2	Process 12
107	Program 7	10	237	Process 1
108	Program 7	13	227	Process 2
109	Program 7	3	214	Process 3
110	Program 7	3	211	Process 4
111	Program 7	24	208	Process 5
112	Program 7	2	184	Process 6
113	Program 7	1	182	Process 7
114	Program 7	21	181	Process 8
115	Program 7	27	160	Process 9
116	Program 7	15	133	Process 10
117	Program 7	22	118	Process 11
118	Program 7	26	96	Process 12
119	Program 7	27	70	Process 13
120	Program 7	22	43	Process 14
121	Program 7	21	21	Process 15
122	Program 8	1	166	Process 1
123	Program 8	18	165	Process 2
124	Program 8	20	147	Process 3
125	Program 8	2	127	Process 4
126	Program 8	10	125	Process 5
127	Program 8	17	115	Process 6
128	Program 8	1	98	Process 7
129	Program 8	12	97	Process 8
130	Program 8	11	85	Process 9
131	Program 8	17	74	Process 10
132	Program 8	12	57	Process 11
133	Program 8	13	45	Process 12
134	Program 8	29	32	Process 13
135	Program 8	3	3	Process 14
136	Program 9	15	231	Process 1
137	Program 9	24	216	Process 2
138	Program 9	23	192	Process 3
139	Program 9	11	169	Process 4
140	Program 9	21	158	Process 5
141	Program 9	20	137	Process 6
142	Program 9	7	117	Process 7
143	Program 9	10	110	Process 8
144	Program 9	24	100	Process 9
145	Program 9	1	76	Process 10
146	Program 9	16	75	Process 11
147	Program 9	10	59	Process 12
148	Program 9	2	49	Process 13
149	Program 9	27	47	Process 14
150	Program 9	20	20	Process 15
151	Program 10	17	232	Process 1
152	Program 10	20	215	Process 2
153	Program 10	21	195	Process 3
154	Program 10	17	174	Process 4
155	Program 10	11	157	Process 5
156	Program 10	27	146	Process 6
157	Program 10	22	119	Process 7
158	Program 10	7	97	Process 8
159	Program 10	18	90	Process 9
160	Program 10	22	72	Process 10
161	Program 10	20	50	Process 11
162	Program 10	16	30	Process 12
163	Program 10	14	14	Process 13
Sheet6

Cell Formulas
Range	Formula
R2:R15	R2	=SUM(Table6[@[Process 1]:[Process 16]])

 

[igold]

Edit: As an update, I have now realized that my code is flawed. Although it is cleaning up the zero value cells, it is not assigning the correct Process number after skipping the zeroes. I am going to try and rework the code to correct it.

In the meantime, hopefully the code I have presented helps you get an idea of what route I am taking to meet your requirements...