```
Sub combination_testing()
' On Error GoTo ErrHandler
1 Application.ScreenUpdating = False
2 Dim data_set As Variant
3 data_set = Sheets("Data").Range("A4:AV75617")
4 Dim i As Long
5 Dim sumall_combination_1y, sumall_combination_2y, sumall_combination_3y, sumall_combination_wnrs_1y, sumall_combination_wnrs_2y, sumall_combination_wnrs_3y, sumall_combination_lsrs_1y, sumall_combination_lsrs_2y, sumall_combination_lsrs_3y, sumall_combination_1, sumall_combination_2, sumall_combination_3, sumall_combination_4, sumall_combination_5, sumall_combination_6, sumall_combination_7, sumall_combination_8, sumall_combination_9, sumall_combination_10, sumall_combination_11, sumall_combination_12, sumall_combinationspx_1, sumall_combinationspx_2, sumall_combinationspx_3, sumall_combinationspx_4, sumall_combinationspx_5, sumall_combinationspx_6, sumall_combinationspx_7, sumall_combinationspx_8, sumall_combinationspx_9, sumall_combinationspx_10, sumall_combinationspx_11, sumall_combinationspx_12 As Double
6 Dim cnt_combination_1y, cnt_combination_2y, cnt_combination_3y, cnt_combination_wnrs_1y, cnt_combination_wnrs_2y, cnt_combination_wnrs_3y, cnt_combination_lsrs_1y, cnt_combination_lsrs_2y, cnt_combination_lsrs_3y, cnt_combination_prof As Long
7 cnt_combination_1y = 0: sumall_combination_1y = 0: cnt_combination_2y = 0: sumall_combination_2y = 0: cnt_combination_3y = 0: sumall_combination_3y = 0: cnt_combination_wnrs_1y = 0: cnt_combination_wnrs_2y = 0: cnt_combination_wnrs_3y = 0: cnt_combination_lsrs_1y = 0: cnt_combination_lsrs_2y = 0: cnt_combination_lsrs_3y = 0: sumall_combination_wnrs_1y = 0: sumall_combination_wnrs_2y = 0: sumall_combination_wnrs_3y = 0: sumall_combination_lsrs_1y = 0: sumall_combination_lsrs_2y = 0: sumall_combination_lsrs_3y = 0: cnt_combination_prof = 0
8 one_change_must = Sheets("Control").Range("D24") 'checking whether conditions are MUST, OTHER or EXCLUDED
9 two_change_must = Sheets("Control").Range("D25")
10 three_change_must = Sheets("Control").Range("D26")
11 ii_must = Sheets("Control").Range("D27")
12 ds_must = Sheets("Control").Range("D28")
13 cm_must = Sheets("Control").Range("D29")
14 ur_must = Sheets("Control").Range("D30")
15 g_must = Sheets("Control").Range("D31")
16 must_checks_total = Sheets("Control").Range("D21")
17 other_checks_total = Sheets("Control").Range("D22")
18 lookback_period = Sheets("Control").Range("F24")
19 ii_threshold = Sheets("Control").Range("G27")
20 cm_threshold = Sheets("Control").Range("G29")
21 ur_threshold = Sheets("Control").Range("G30")
22 g_threshold = Sheets("Control").Range("G31")
23 For i = lookback_period + 1 To UBound(data_set, 1)
24 x_member = data_set(i, 8)
25 company_name = data_set(i, 3)
26 company_name_prev = data_set(i - lookback_period, 3)
27 g_score = data_set(i, 11)
28 ur_score = data_set(i, 13)
30 cm_score = data_set(i, 12)
31 For j = i - lookback_period To i 'combination change trigger
32 one_change = data_set(j, 15)
33 two_change = data_set(j, 16)
34 three_change = data_set(j, 17)
35 ii = data_set(j, 18)
36 ds = data_set(j, 10)
37 If one_change = 1 Then 'not sure if this will work; one_change is not a single value but several values
38 one_change_flag = 1
304 one_change_flags = one_change_flags + 1
39 End If
40 If two_change = 1 Then
41 two_change_flag = 1
42 End If
43 If three_change = 1 Then
44 three_change_flag = 1
45 End If
46 If ii > ii_threshold Then
47 ii_flag = 1
48 End If
49 If ds = 1 Then
50 ds_flag = 1
51 End If
52 Next j
53 If one_change_flag = 1 And one_change_must = 1 Then
54 one_must_check = 1
55 ElseIf one_change_flag = 1 And one_change_must = 3 Then
57 one_other_check = 1
58 Else
59 one_other_check = 0: one_must_check = 0
60 End If
61 If two_change_flag = 1 And two_change_must = 1 Then
62 two_must_check = 1
63 ElseIf two_change_flag = 1 And two_change_must = 3 Then
65 two_other_check = 1
66 Else
67 two_must_check = 0: two_other_check = 0
68 End If
69 If three_change_flag = 1 And three_change_must = 1 Then
70 three_must_check = 1
71 ElseIf three_change_flag = 1 And three_change_must = 3 Then
73 three_other_check = 1
74 Else
75 three_must_check = 0: three_other_check = 0
76 End If
77 If ii_flag = 1 And ii_must = 1 Then
78 ii_must_check = 1
79 ElseIf ii_flag = 1 And ii_must = 3 Then
81 ii_other_check = 1
82 Else
83 ii_must_check = 0: ii_other_check = 0
84 End If
85 If ds_flag = 1 And ds_must = 1 Then
86 ds_must_check = 1
87 ElseIf ds_flag = 1 And ds_must = 3 Then
89 ds_other_check = 1
90 Else
91 ds_must_check = 0: ds_other_check = 0
92 End If
93 If g_score > g_threshold And g_must = 1 Then
94 g_must_check = 1
95 ElseIf g_score > g_threshold And g_must = 3 Then
97 g_other_check = 1
98 Else
99 g_must_check = 0: g_other_check = 0
100 End If
101 If acquisitions_score > acquisitions_threshold And acquisitions_must = 1 Then
102 acquisitions_must_check = 1
103 ElseIf acquisitions_score > acquisitions_threshold And acquisitions_must = 3 Then
105 acquisitions_other_check = 1
106 Else
107 acquisitions_must_check = 0: acquisitions_other_check = 0
108 End If
109 If overdistribution_score > overdistribution_threshold And overdistribution_must = 1 Then
110 overdistribution_must_check = 1
111 ElseIf overdistribution_score > overdistribution_threshold And overdistribution_must = 3 Then
113 overdistribution_other_check = 1
114 Else
115 overdistribution_must_check = 0: overdistribution_other_check = 0
116 End If
221 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev Then 'acquisitions score overall average return
222 sumall_combination_1y = sumall_combination_1y + data_set(i, 19)
223 sumall_combination_2y = sumall_combination_2y + data_set(i, 20)
224 sumall_combination_3y = sumall_combination_3y + data_set(i, 21)
225 End If
117 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev Then
118 sumall_combination_1 = sumall_combination_1 + data_set(i, 23)
119 sumall_combination_2 = sumall_combination_2 + data_set(i, 24)
120 sumall_combination_3 = sumall_combination_3 + data_set(i, 25)
121 sumall_combination_4 = sumall_combination_4 + data_set(i, 26)
122 sumall_combination_5 = sumall_combination_5 + data_set(i, 27)
123 sumall_combination_6 = sumall_combination_6 + data_set(i, 28)
124 sumall_combination_7 = sumall_combination_7 + data_set(i, 29)
125 sumall_combination_8 = sumall_combination_8 + data_set(i, 30)
126 sumall_combination_9 = sumall_combination_9 + data_set(i, 31)
127 sumall_combination_10 = sumall_combination_10 + data_set(i, 32)
128 sumall_combination_11 = sumall_combination_11 + data_set(i, 33)
129 sumall_combination_12 = sumall_combination_12 + data_set(i, 34)
130 sumall_combinationspx_1 = sumall_combinationspx_1 + data_set(i, 36)
131 sumall_combinationspx_2 = sumall_combinationspx_2 + data_set(i, 37)
132 sumall_combinationspx_3 = sumall_combinationspx_3 + data_set(i, 38)
133 sumall_combinationspx_4 = sumall_combinationspx_4 + data_set(i, 39)
134 sumall_combinationspx_5 = sumall_combinationspx_5 + data_set(i, 40)
135 sumall_combinationspx_6 = sumall_combinationspx_6 + data_set(i, 41)
136 sumall_combinationspx_7 = sumall_combinationspx_7 + data_set(i, 42)
137 sumall_combinationspx_8 = sumall_combinationspx_8 + data_set(i, 43)
138 sumall_combinationspx_9 = sumall_combinationspx_9 + data_set(i, 44)
139 sumall_combinationspx_10 = sumall_combinationspx_10 + data_set(i, 45)
140 sumall_combinationspx_11 = sumall_combinationspx_11 + data_set(i, 46)
141 sumall_combinationspx_12 = sumall_combinationspx_12 + data_set(i, 47)
142 cnt_combination_prof = cnt_combination_prof + 1
143 End If
144 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 19) > 0 Then 'combination change 1y winners
145 sumall_combination_wnrs_1y = sumall_combination_wnrs_1y + data_set(i, 19)
146 cnt_combination_wnrs_1y = cnt_combination_wnrs_1y + 1
147 End If
148 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 19) < 0 Then 'combination change 1y losers
149 sumall_combination_lsrs_1y = sumall_combination_lsrs_1y + data_set(i, 19)
150 cnt_combination_lsrs_1y = cnt_combination_lsrs_1y + 1
151 End If
152 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 20) > 0 Then 'combination change 2y winners
153 sumall_combination_wnrs_2y = sumall_combination_wnrs_2y + data_set(i, 20)
154 cnt_combination_wnrs_2y = cnt_combination_wnrs_2y + 1
155 End If
156 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 20) < 0 Then 'combination change 2y losers
157 sumall_combination_lsrs_2y = sumall_combination_lsrs_2y + data_set(i, 20)
158 cnt_combination_lsrs_2y = cnt_combination_lsrs_2y + 1
159 End If
160 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 21) > 0 Then 'combination change 3y winners
161 sumall_combination_wnrs_3y = sumall_combination_wnrs_3y + data_set(i, 21)
162 cnt_combination_wnrs_3y = cnt_combination_wnrs_3y + 1
163 End If
164 If (one_must_check + two_must_check + three_must_check + ii_must_check + resegmentation_must_check + g_must_check + acquisitions_must_check + overdistribution_must_check) = must_checks_total And (one_other_check + two_other_check + three_other_check + ii_other_check + g_other_check + acquisitions_other_check + resegmentation_other_check + overdistribution_other_check) >= other_checks_total And spx_member = 1 And company_name = company_name_prev And data_set(i, 21) < 0 Then 'combination change 3y losers
165 sumall_combination_lsrs_3y = sumall_combination_lsrs_3y + data_set(i, 21)
166 cnt_combination_lsrs_3y = cnt_combination_lsrs_3y + 1
167 End If
168 Next i
Application.ScreenUpdating = True
End Sub
```