Optimizing VBA for Speed

[Veritan]

Hello all. I am trying to speed up some code that I have written. What I have works perfectly fine, but is quite slow. I would like to see if there is a way to increase the speed.

My workbook consists of 3 sheets. The first tab (named "Access Data") contains a table (named "tblData") with 5 fields. The headers in sequential order are "User", "Dept", "Module", "Header", and "Job Name". The 2 relevant fields are the "User" and "Job Name" fields. The "User" field contains a series of user names. The "Job Name" field contains all the different jobs that they are able to perform. The table is sorted first by "User", then by "Job Name", both in ascending order. Each User can have multiple jobs, but a given job can only appear once for a specific user. However, a given job may appear for more than 1 user.

The 2nd sheet (named "SOD Testing") is just a repository for the results of the code execution. Cell B1 contains a simple title string ("List of Duties/Jobs"). Cell B2 contains the formula

=SORT(UNIQUE(tblData[Job Name]))

Cell C1 contains the formula

=TRANSPOSE(SORT(UNIQUE(tblData[Job Name])))

This results in sorted lists of the unique Job Names. The rest of this sheet is filled out by VBA.

The 3rd sheet (named "SOD Notifications") is used to hold a list of the users and the jobs that they perform. However, only certain users and jobs qualify to be on this list (I'll explain more later). Range A1:C1 contains some simple headers ("User", "Job Name 1", "Job Name 2") and the rest of the sheet is blank, but is filled out by VBA.

My goal is 2-fold. First, based on the grid established on "SOD Testing", I want to know how many users are able to do each job listed there. For example, if cell B4 contains "Checks and Registers" and cell E1 contains "Manual Checks", then cell E4 should contain the number of users who are able to do both "Checks and Registers" and "Manual Checks". If there are 3 users that are able to do both jobs, then cell E4 should contain the number 3. Second, certain jobs are not allowed to be performed by the same user. On the "SOD Testing" sheet, the intersecting cell of these jobs is highlighted in red. For example, if cell B10 contains "Void Checks" and cell F1 contains "Prepare Check Proofing" and these two jobs are not allowed to be performed by the same user, then cell F10 would be highlighted in red. This is done manually and is done before running any code.

If my code detects that there is a conflict (in other words, if it identifies that the same user is allowed to perform two conflicting jobs), then it lists the User and both Job Names on the "SOD Notifications" tab starting in cell A2.

Currently, my tblData contains almost 2,000 records, and there are almost 300 unique Job Names. This is a smaller sampling size, and will get larger when we use this for other scenarios. As a result, I'm hoping to find a way to run this code quickly. I have come up with 2 different solutions. Both of these work perfectly. However, they are both fairly slow. In order to speed up the process, I am hoping that somebody can provide some insight into speeding this up. Thank you very much for any and all assistance!

Edit: I'm reasonably comfortable with Power Query and Power Pivot, so solutions involving them are perfectly fine as well.

Note: Both code segments are called while the "SOD Testing" sheet is the active sheet.

Version 1 (no arrays)

Sub Check_Duties()
    Dim wsData As Worksheet, strFirstAddress As String
    Dim r As Range, strJob As String, rngJob As Range, strUser As String, intOccurrences As Integer
    Dim intTotal As Integer, wsNotifications As Worksheet, rngDuties As Range, msgContinue As VbMsgBoxResult
    Dim loData As ListObject, lngFoundRow As Long
   
    msgContinue = MsgBox(Prompt:="CAUTION!! This process can take several minutes to complete. " & _
                                 "During this time, you will not be able to use Excel. " & _
                                 "Are you sure you wish to continue?", _
                         Buttons:=vbYesNo + vbExclamation + vbDefaultButton2, _
                         Title:="Extended Processing Time Required")
    If msgContinue = vbNo Then Exit Sub
                   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    'Set the major variables that will be used throughout the code
    Set wsData = Sheets("Access Data")
    Set wsNotifications = Sheets("SOD Notifications")
    Set loData = wsData.ListObjects(1)
    Set rngDuties = Range(Cells(2, "C"), Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
   
    'Clear any existing data to start fresh. Note that cell fill colors are intentionally left intact.
    wsNotifications.Range("A2:C" & wsNotifications.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row).ClearContents
    rngDuties.ClearContents
   
    For Each r In rngDuties
        'Make sure that the row and column values are different.
        If Cells(r.Row, "B") <> Cells(1, r.Column) Then
            strJob = Cells(r.Row, "B")
            Set rngJob = loData.ListColumns("Job Name").DataBodyRange.Find(What:=strJob, LookAt:=xlWhole)
            If Not rngJob Is Nothing Then
                lngFoundRow = loData.ListRows(rngJob.Row - loData.HeaderRowRange.Row).Index
                'Set the exit condition for the Do loop.
                strFirstAddress = rngJob.Address
                'Reset the count of users.
                intTotal = 0
                Do
                    intOccurrences = 0
                    strUser = loData.ListColumns("User").DataBodyRange(lngFoundRow)
                    'Find out if there are any occurrences where the given user has the Job Name that is presented in Row 1 on the active sheet.
                    intOccurrences = WorksheetFunction.CountIfs(loData.ListColumns("User").DataBodyRange, strUser, loData.ListColumns("Job Name").DataBodyRange, Cells(1, r.Column))
                    'If there are any undesired combinations of duties, highlighting the cell in red will cause the SOD Notifications tab to _
                     display the user and their conflict of duties.
                    If r.Interior.Color = vbRed And intOccurrences > 0 Then
                        With wsNotifications
                            .Cells(Rows.Count, "A").End(xlUp).Offset(1) = strUser
                            .Cells(Rows.Count, "B").End(xlUp).Offset(1) = strJob
                            .Cells(Rows.Count, "C").End(xlUp).Offset(1) = Cells(1, r.Column)
                        End With
                    End If
                    'Track the total number of users with the given Job Name combination.
                    intTotal = intTotal + intOccurrences
                    Set rngJob = loData.ListColumns("Job Name").DataBodyRange.FindNext(rngJob)
                Loop Until rngJob.Address = strFirstAddress
                'Print the final total in the cell.
                r = intTotal
            End If
        End If
    Next r
   
   With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

Version 2 (with arrays)

Sub Check_Duties_Array()
    Dim wsData As Worksheet, wsNotifications As Worksheet, r As Range, i As Long, j As Integer, intTotal As Integer
    Dim msgContinue As VbMsgBoxResult, rngDuties As Range, arrData As Variant
    Dim strJob As String, strCompare As String, blnNotify As Boolean
   
    msgContinue = MsgBox(Prompt:="CAUTION!! This process can take several minutes to complete. " & _
                                 "During this time, you will not be able to use Excel. " & _
                                 "Are you sure you wish to continue?", _
                         Buttons:=vbYesNo + vbExclamation + vbDefaultButton2, _
                         Title:="Extended Processing Time Required")
    If msgContinue = vbNo Then Exit Sub
                   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    'Set the major variables that will be used throughout the code
    Set wsData = Sheets("Access Data")
    Set wsNotifications = Sheets("SOD Notifications")
    Set rngDuties = Range(Cells(2, "C"), Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
   
    'Clear any existing data to start fresh. Note that cell fill colors are intentionally left intact.
    wsNotifications.Range("A2:C" & wsNotifications.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row).ClearContents
    rngDuties.ClearContents
   
    'Create a 2 dimensional array with the "User" and "Job Name" fields in it.
    arrData = wsData.ListObjects(1).DataBodyRange.Value2
    arrData = Application.Index(arrData, Evaluate("row(1:" & UBound(arrData) & ")"), Array(1, 5))
       
    For Each r In rngDuties
        strJob = Cells(r.Row, "B")
        strCompare = Cells(1, r.Column)
        If r.Interior.Color = vbRed Then blnNotify = True
        'Make sure that the row and column values are different.
        If strJob <> strCompare Then
            'Reset the count of users.
            intTotal = 0
            For i = 1 To UBound(arrData, 1)
                If arrData(i, 2) = strJob Then
                    For j = 1 To UBound(arrData, 1)
                        If arrData(j, 1) = arrData(i, 1) And arrData(j, 2) = strCompare Then
                            intTotal = intTotal + 1
                            'If there are any undesired combinations of duties, highlighting the cell in red will cause the SOD Notifications tab to _
                             display the user and their conflict of duties.
                            If blnNotify Then
                                With wsNotifications
                                    .Cells(Rows.Count, "A").End(xlUp).Offset(1) = arrData(i, 1)
                                    .Cells(Rows.Count, "B").End(xlUp).Offset(1) = arrData(i, 2)
                                    .Cells(Rows.Count, "C").End(xlUp).Offset(1) = strCompare
                                End With
                            End If
                        End If
                    Next j
                End If
            Next i
        r = intTotal
        blnNotify = False
        End If
    Next r
   
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

 

[DanteAmor]

You could share a sample of your data from sheets 1 and 2. And the expected result on sheet 3.
To be able to test the macros, understand the operation and find to improve the performance of the process.

For examples use XL2BB tool minisheet

 

[Veritan]

Certainly, thanks for pointing that out. This is my first time using XL2BB, so hopefully I am doing this right.

Sample data from my "Access Data" sheet. User ID's have been replaced with generic ID's and some Jobs have been removed to keep the size down, but otherwise, all data is the same as the original.

SOD Testing v2.xlsm
	A	B	C	D	E
1	User	Dept	Module	Header	Job Name
2	User1	Shared Services	Accounts Payable	Transactions	ACH Export
3	User1	Shared Services	Accounts Payable	Transactions	AP Batch Entry - New
4	User1	Shared Services	Accounts Payable	Transactions	Create AP Invoice from Receipts
5	User1	Shared Services	Accounts Payable	Transactions	Manual Checks
6	User2	Shared Services	Accounts Payable	Transactions	ACH Export
7	User2	Shared Services	Accounts Payable	Transactions	AP Batch Entry - New
8	User2	Shared Services	Accounts Payable	Transactions	Create AP Invoice from Receipts
9	User2	Shared Services	Accounts Payable	Transactions	Manual Checks
10	User3	Customer Service	Order Entry	Transactions	Apply Router Changes to Rate Tables
11	User3	Customer Service	Order Entry	Transactions	Bill of Lading - Delete
12	User3	Customer Service	Order Entry	Transactions	Clear EDI Invoice Selection File
13	User3	Customer Service	Order Entry	Transactions	Generate EDI Invoices
14	User3	Customer Service	Order Entry	Transactions	Invoicing
15	User3	Customer Service	Order Entry	Transactions	Master Bill of Lading - Open
16	User3	Customer Service	Inventory	Transactions	Calculate Next Cycle Date
17	User3	Customer Service	Inventory	Transactions	Inventory Transfers
18	User3	Customer Service	Inventory	Transactions	Part Allocations
19	User4	Purchasing	Purchasing	Transactions	Last Cost vs. Standard Cost
20	User4	Purchasing	Purchasing	Transactions	PO Receipts by Container
21	User4	Purchasing	Inventory	Transactions	Calculate Next Cycle Date
22	User4	Purchasing	Inventory	Transactions	Inventory Transfers
23	User4	Purchasing	Inventory	Transactions	Part Allocations
24	User4	Purchasing	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
25	User4	Purchasing	Shop Floor Control	Transactions	Edit Flexible Schedule
26	User4	Purchasing	Shop Floor Control	Administration	Delete Work Orders
27	User4	Purchasing	Advanced Planning System	Transactions	Moveable Dispatch Chart
28	User4	Purchasing	On Line System	Transactions	Daily Balancing (No Dollars)
29	User4	Purchasing	Shipping & Receiving	Transactions	PO Receipts by Container
30	User5	Plant Supervisor	Order Entry	Transactions	Apply Router Changes to Rate Tables
31	User5	Plant Supervisor	Order Entry	Transactions	Bill of Lading - Delete
32	User5	Plant Supervisor	Order Entry	Transactions	Clear EDI Invoice Selection File
33	User5	Plant Supervisor	Order Entry	Transactions	Generate EDI Invoices
34	User5	Plant Supervisor	Order Entry	Transactions	Invoicing
35	User5	Plant Supervisor	Order Entry	Transactions	Master Bill of Lading - Open
36	User5	Plant Supervisor	Inventory	Transactions	Calculate Next Cycle Date
37	User5	Plant Supervisor	Inventory	Transactions	Inventory Transfers
38	User5	Plant Supervisor	Inventory	Transactions	Part Allocations
39	User5	Plant Supervisor	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
40	User5	Plant Supervisor	Shop Floor Control	Transactions	Edit Flexible Schedule
41	User5	Plant Supervisor	On Line System	Transactions	Daily Balancing (No Dollars)
42	User5	Plant Supervisor	Shipping & Receiving	Transactions	PO Receipts by Container
43	User6	Operations	Inventory	Transactions	Calculate Next Cycle Date
44	User6	Operations	Inventory	Transactions	Inventory Transfers
45	User6	Operations	Inventory	Transactions	Part Allocations
46	User6	Operations	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
47	User6	Operations	Shop Floor Control	Transactions	Edit Flexible Schedule
48	User6	Operations	Shipping & Receiving	Transactions	PO Receipts by Container
49	User7	Shared Services	Accounts Payable	Transactions	ACH Export
50	User7	Shared Services	Accounts Payable	Transactions	AP Batch Entry - New
51	User7	Shared Services	Accounts Payable	Transactions	Create AP Invoice from Receipts
52	User7	Shared Services	Accounts Payable	Transactions	Manual Checks
53	User8	Shared Services	Accounts Payable	Transactions	ACH Export
54	User8	Shared Services	Accounts Payable	Transactions	AP Batch Entry - New
55	User8	Shared Services	Accounts Payable	Transactions	Create AP Invoice from Receipts
56	User8	Shared Services	Accounts Payable	Transactions	Manual Checks
57	User10	Shared Services	Accounts Payable	Transactions	ACH Export
58	User10	Shared Services	Accounts Payable	Transactions	AP Batch Entry - New
59	User10	Shared Services	Accounts Payable	Transactions	Create AP Invoice from Receipts
60	User10	Shared Services	Accounts Payable	Transactions	Manual Checks
61	User11	Customer Service	Order Entry	Transactions	Apply Router Changes to Rate Tables
62	User11	Customer Service	Order Entry	Transactions	Bill of Lading - Delete
63	User11	Customer Service	Order Entry	Transactions	Clear EDI Invoice Selection File
64	User11	Customer Service	Order Entry	Transactions	Generate EDI Invoices
65	User11	Customer Service	Order Entry	Transactions	Invoicing
66	User11	Customer Service	Order Entry	Transactions	Master Bill of Lading - Open
67	User12	Purchasing	Purchasing	Transactions	Last Cost vs. Standard Cost
68	User12	Purchasing	Purchasing	Transactions	PO Receipts by Container
69	User12	Purchasing	Inventory	Transactions	Calculate Next Cycle Date
70	User12	Purchasing	Inventory	Transactions	Inventory Transfers
71	User12	Purchasing	Inventory	Transactions	Part Allocations
72	User12	Purchasing	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
73	User12	Purchasing	Shop Floor Control	Transactions	Edit Flexible Schedule
74	User12	Purchasing	Shop Floor Control	Administration	Delete Work Orders
75	User12	Purchasing	Advanced Planning System	Transactions	Moveable Dispatch Chart
76	User12	Purchasing	On Line System	Transactions	Daily Balancing (No Dollars)
77	User12	Purchasing	Shipping & Receiving	Transactions	PO Receipts by Container
78	User13	Customer Service	Order Entry	Transactions	Apply Router Changes to Rate Tables
79	User13	Customer Service	Order Entry	Transactions	Bill of Lading - Delete
80	User13	Customer Service	Order Entry	Transactions	Clear EDI Invoice Selection File
81	User13	Customer Service	Order Entry	Transactions	Generate EDI Invoices
82	User13	Customer Service	Order Entry	Transactions	Invoicing
83	User13	Customer Service	Order Entry	Transactions	Master Bill of Lading - Open
84	User13	Customer Service	Inventory	Transactions	Calculate Next Cycle Date
85	User13	Customer Service	Inventory	Transactions	Inventory Transfers
86	User13	Customer Service	Inventory	Transactions	Part Allocations
87	User14	Purchasing	Purchasing	Transactions	Last Cost vs. Standard Cost
88	User14	Purchasing	Purchasing	Transactions	PO Receipts by Container
89	User14	Purchasing	Inventory	Transactions	Calculate Next Cycle Date
90	User14	Purchasing	Inventory	Transactions	Inventory Transfers
91	User14	Purchasing	Inventory	Transactions	Part Allocations
92	User14	Purchasing	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
93	User14	Purchasing	Shop Floor Control	Transactions	Edit Flexible Schedule
94	User14	Purchasing	Shipping & Receiving	Transactions	PO Receipts by Container
95	User15	Kitting	Inventory	Transactions	Calculate Next Cycle Date
96	User15	Kitting	Inventory	Transactions	Inventory Transfers
97	User15	Kitting	Inventory	Transactions	Part Allocations
98	User15	Kitting	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
99	User15	Kitting	Shop Floor Control	Transactions	Edit Flexible Schedule
100	User16	Project Manager	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
101	User16	Project Manager	Shop Floor Control	Transactions	Edit Flexible Schedule
102	User16	Project Manager	Advanced Planning System	Transactions	Moveable Dispatch Chart
103	User17	Purchasing	Purchasing	Transactions	Last Cost vs. Standard Cost
104	User17	Purchasing	Purchasing	Transactions	PO Receipts by Container
105	User17	Purchasing	Purchasing	Administration	Purchase Orders In Use
106	User17	Purchasing	Inventory	Transactions	Calculate Next Cycle Date
107	User17	Purchasing	Inventory	Transactions	Inventory Transfers
108	User17	Purchasing	Inventory	Transactions	Part Allocations
109	User17	Purchasing	Inventory	Administration	Inventory Master BIN Mass Update (c)
110	User17	Purchasing	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
111	User17	Purchasing	Shop Floor Control	Transactions	Edit Flexible Schedule
112	User17	Purchasing	Advanced Planning System	Transactions	Moveable Dispatch Chart
113	User17	Purchasing	On Line System	Transactions	Daily Balancing (No Dollars)
114	User17	Purchasing	Shipping & Receiving	Transactions	PO Receipts by Container
115	User18	Operations	Inventory	Transactions	Calculate Next Cycle Date
116	User18	Operations	Inventory	Transactions	Inventory Transfers
117	User18	Operations	Inventory	Transactions	Part Allocations
118	User18	Operations	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
119	User18	Operations	Shop Floor Control	Transactions	Edit Flexible Schedule
120	User18	Operations	Shipping & Receiving	Transactions	PO Receipts by Container
121	User19	Purchasing	Inventory	Transactions	Calculate Next Cycle Date
122	User19	Purchasing	Inventory	Transactions	Inventory Transfers
123	User19	Purchasing	Inventory	Transactions	Part Allocations
124	User19	Purchasing	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
125	User19	Purchasing	Shop Floor Control	Transactions	Edit Flexible Schedule
126	User20	Shared Services	Accounts Receivable	Transactions	AR Batches - Open
127	User20	Shared Services	Accounts Receivable	Transactions	AR Remittance from EDI Transactions
128	User20	Shared Services	Accounts Receivable	Transactions	Calculate Commissions
129	User20	Shared Services	Accounts Receivable	Transactions	Convert Prospect to Customer
130	User20	Shared Services	Accounts Receivable	Transactions	Factor Cash Receipt
131	User20	Shared Services	Order Entry	Transactions	Apply Router Changes to Rate Tables
132	User20	Shared Services	Order Entry	Transactions	Bill of Lading - Delete
133	User20	Shared Services	Order Entry	Transactions	Clear EDI Invoice Selection File
134	User20	Shared Services	Order Entry	Transactions	Generate EDI Invoices
135	User20	Shared Services	Order Entry	Transactions	Invoicing
136	User20	Shared Services	Order Entry	Transactions	Master Bill of Lading - Open
137	User21	Operations	Inventory	Transactions	Calculate Next Cycle Date
138	User21	Operations	Inventory	Transactions	Inventory Transfers
139	User21	Operations	Inventory	Transactions	Part Allocations
140	User22	Operations	Order Entry	Transactions	Apply Router Changes to Rate Tables
141	User22	Operations	Order Entry	Transactions	Bill of Lading - Delete
142	User22	Operations	Order Entry	Transactions	Clear EDI Invoice Selection File
143	User22	Operations	Order Entry	Transactions	Generate EDI Invoices
144	User22	Operations	Order Entry	Transactions	Invoicing
145	User22	Operations	Order Entry	Transactions	Master Bill of Lading - Open
146	User22	Operations	Purchasing	Transactions	Last Cost vs. Standard Cost
147	User22	Operations	Purchasing	Transactions	PO Receipts by Container
148	User22	Operations	Inventory	Transactions	Calculate Next Cycle Date
149	User22	Operations	Inventory	Transactions	Inventory Transfers
150	User22	Operations	Inventory	Transactions	Part Allocations
151	User22	Operations	Quality System	Transactions	Maintain Cause and Corrective Action
152	User22	Operations	Quality System	Transactions	Maintain Engineering Change Control - Delete
153	User22	Operations	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
154	User22	Operations	Shop Floor Control	Transactions	Edit Flexible Schedule
155	User22	Operations	Advanced Planning System	Transactions	Moveable Dispatch Chart
156	User22	Operations	Shipping & Receiving	Transactions	PO Receipts by Container
157	User23	Operations	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
158	User23	Operations	Shop Floor Control	Transactions	Edit Flexible Schedule
159	User25	Receiving	Inventory	Transactions	Calculate Next Cycle Date
160	User25	Receiving	Inventory	Transactions	Inventory Transfers
161	User25	Receiving	Inventory	Transactions	Part Allocations
162	User25	Receiving	Shipping & Receiving	Transactions	PO Receipts by Container
163	User26	Receiving	Inventory	Transactions	Calculate Next Cycle Date
164	User26	Receiving	Inventory	Transactions	Inventory Transfers
165	User26	Receiving	Inventory	Transactions	Part Allocations
166	User26	Receiving	Shipping & Receiving	Transactions	PO Receipts by Container
167	User27	Receiving	Inventory	Transactions	Calculate Next Cycle Date
168	User27	Receiving	Inventory	Transactions	Inventory Transfers
169	User27	Receiving	Inventory	Transactions	Part Allocations
170	User28	Accounting	Accounts Payable	Transactions	ACH Export
171	User28	Accounting	Accounts Payable	Transactions	AP Batch Entry - New
172	User28	Accounting	Accounts Payable	Transactions	Create AP Invoice from Receipts
173	User28	Accounting	Accounts Payable	Transactions	Manual Checks
174	User28	Accounting	Accounts Payable	Administration	Rebuild Alternate Keys - Vendors
175	User28	Accounting	General Ledger	Transactions	Create JE for Receipt of Container
176	User28	Accounting	General Ledger	Transactions	GL Currency Conversion
177	User28	Accounting	General Ledger	Transactions	Journal Entries - Delete
178	User28	Accounting	General Ledger	Administration	GL Batches In Use
179	User28	Accounting	General Ledger	Administration	Net Change Summary by Account
180	User28	Accounting	Accounts Receivable	Transactions	AR Batches - Open
181	User28	Accounting	Accounts Receivable	Transactions	AR Remittance from EDI Transactions
182	User28	Accounting	Accounts Receivable	Transactions	Calculate Commissions
183	User28	Accounting	Accounts Receivable	Transactions	Convert Prospect to Customer
184	User28	Accounting	Accounts Receivable	Transactions	Factor Cash Receipt
185	User28	Accounting	Accounts Receivable	Administration	Aging and Purge Zeroed Invoices
186	User28	Accounting	Accounts Receivable	Administration	Balance A/R Open Item Currency Exchange Amounts
187	User28	Accounting	Accounts Receivable	Administration	Credit Card Credit Form
188	User28	Accounting	Order Entry	Transactions	Apply Router Changes to Rate Tables
189	User28	Accounting	Order Entry	Transactions	Bill of Lading - Delete
190	User28	Accounting	Order Entry	Transactions	Clear EDI Invoice Selection File
191	User28	Accounting	Order Entry	Transactions	Generate EDI Invoices
192	User28	Accounting	Order Entry	Transactions	Invoicing
193	User28	Accounting	Order Entry	Transactions	Master Bill of Lading - Open
194	User28	Accounting	Purchasing	Transactions	Last Cost vs. Standard Cost
195	User28	Accounting	Purchasing	Transactions	PO Receipts by Container
196	User28	Accounting	Inventory	Transactions	Calculate Next Cycle Date
197	User28	Accounting	Inventory	Transactions	Inventory Transfers
198	User28	Accounting	Inventory	Transactions	Part Allocations
199	User28	Accounting	Quality System	Transactions	Maintain Cause and Corrective Action
200	User28	Accounting	Quality System	Transactions	Maintain Engineering Change Control - Delete
201	User28	Accounting	Payroll	Transactions	Journal Entries - Payroll Journal Entries
202	User28	Accounting	Payroll	Transactions	Manual/Void Check Processing - Manual Checks
203	User28	Accounting	Payroll	Administration	Employee Name Separation
204	User28	Accounting	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
205	User28	Accounting	Shop Floor Control	Transactions	Edit Flexible Schedule
206	User28	Accounting	Advanced Planning System	Transactions	Moveable Dispatch Chart
207	User28	Accounting	On Line System	Transactions	Daily Balancing (No Dollars)
208	User28	Accounting	Shipping & Receiving	Transactions	PO Receipts by Container
209	User29	IT	Inventory	Administration	Inventory Master BIN Mass Update (c)
210	User32	Accounting	Accounts Payable	Transactions	ACH Export
211	User32	Accounting	Accounts Payable	Transactions	AP Batch Entry - New
212	User32	Accounting	Accounts Payable	Transactions	Create AP Invoice from Receipts
213	User32	Accounting	Accounts Payable	Transactions	Manual Checks
214	User32	Accounting	Accounts Payable	Administration	Rebuild Alternate Keys - Vendors
215	User32	Accounting	General Ledger	Transactions	Create JE for Receipt of Container
216	User32	Accounting	General Ledger	Transactions	GL Currency Conversion
217	User32	Accounting	General Ledger	Transactions	Journal Entries - Delete
218	User32	Accounting	General Ledger	Administration	GL Batches In Use
219	User32	Accounting	General Ledger	Administration	Net Change Summary by Account
220	User32	Accounting	Accounts Receivable	Transactions	AR Batches - Open
221	User32	Accounting	Accounts Receivable	Transactions	AR Remittance from EDI Transactions
222	User32	Accounting	Accounts Receivable	Transactions	Calculate Commissions
223	User32	Accounting	Accounts Receivable	Transactions	Convert Prospect to Customer
224	User32	Accounting	Accounts Receivable	Transactions	Factor Cash Receipt
225	User32	Accounting	Accounts Receivable	Administration	Aging and Purge Zeroed Invoices
226	User32	Accounting	Accounts Receivable	Administration	Balance A/R Open Item Currency Exchange Amounts
227	User32	Accounting	Accounts Receivable	Administration	Credit Card Credit Form
228	User32	Accounting	Order Entry	Transactions	Apply Router Changes to Rate Tables
229	User32	Accounting	Order Entry	Transactions	Bill of Lading - Delete
230	User32	Accounting	Order Entry	Transactions	Clear EDI Invoice Selection File
231	User32	Accounting	Order Entry	Transactions	Generate EDI Invoices
232	User32	Accounting	Order Entry	Transactions	Invoicing
233	User32	Accounting	Order Entry	Transactions	Master Bill of Lading - Open
234	User32	Accounting	Order Entry	Administration	Apply Cust Values to Ship-To Record
235	User32	Accounting	Order Entry	Administration	Delete Credit Card Audit File
236	User32	Accounting	Order Entry	Administration	Rate Table Copy
237	User32	Accounting	Business Intelligence	Administration	Initialize Financial Statements
238	User32	Accounting	Purchasing	Transactions	Last Cost vs. Standard Cost
239	User32	Accounting	Purchasing	Transactions	PO Receipts by Container
240	User32	Accounting	Purchasing	Administration	Purchase Orders In Use
241	User32	Accounting	Inventory	Transactions	Calculate Next Cycle Date
242	User32	Accounting	Inventory	Transactions	Inventory Transfers
243	User32	Accounting	Inventory	Transactions	Part Allocations
244	User32	Accounting	Inventory	Administration	Maintain Item Master User Titles (Lt/Bn)
245	User32	Accounting	Inventory	Administration	Refresh Inv Alt Cost from Latest Cost
246	User32	Accounting	Quality System	Transactions	Maintain Cause and Corrective Action
247	User32	Accounting	Quality System	Transactions	Maintain Engineering Change Control - Delete
248	User32	Accounting	Quality System	Administration	Purge Quality
249	User32	Accounting	Payroll	Transactions	Journal Entries - Payroll Journal Entries
250	User32	Accounting	Payroll	Transactions	Manual/Void Check Processing - Manual Checks
251	User32	Accounting	Payroll	Administration	Employee Name Separation
252	User32	Accounting	Bill Of Material	Administration	BOM In Use
253	User32	Accounting	Estimating / Routing & Quote Management	Administration	Calculate Workcenter MTD and YTD Hours
254	User32	Accounting	Estimating / Routing & Quote Management	Administration	Mass Replace Materials
255	User32	Accounting	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
256	User32	Accounting	Shop Floor Control	Transactions	Edit Flexible Schedule
257	User32	Accounting	Shop Floor Control	Administration	Delete Work Orders
258	User32	Accounting	Shop Floor Control	Administration	Mass Replace Workcenters in Work Orders
259	User32	Accounting	Advanced Planning System	Transactions	Moveable Dispatch Chart
260	User32	Accounting	Advanced Planning System	Administration	Clear Forecast Data
261	User32	Accounting	On Line System	Transactions	Daily Balancing (No Dollars)
262	User32	Accounting	Shipping & Receiving	Transactions	PO Receipts by Container
263	User33	HR	Payroll	Transactions	Journal Entries - Payroll Journal Entries
264	User33	HR	Payroll	Transactions	Manual/Void Check Processing - Manual Checks
265	User33	HR	Payroll	Administration	Employee Name Separation
266	User34	Accounting	Accounts Payable	Transactions	ACH Export
267	User34	Accounting	Accounts Payable	Transactions	AP Batch Entry - New
268	User34	Accounting	Accounts Payable	Transactions	Create AP Invoice from Receipts
269	User34	Accounting	Accounts Payable	Transactions	Manual Checks
270	User34	Accounting	Accounts Payable	Administration	Rebuild Alternate Keys - Vendors
271	User34	Accounting	General Ledger	Transactions	Create JE for Receipt of Container
272	User34	Accounting	General Ledger	Transactions	GL Currency Conversion
273	User34	Accounting	General Ledger	Transactions	Journal Entries - Delete
274	User34	Accounting	General Ledger	Administration	GL Batches In Use
275	User34	Accounting	General Ledger	Administration	Net Change Summary by Account
276	User34	Accounting	Accounts Receivable	Transactions	AR Batches - Open
277	User34	Accounting	Accounts Receivable	Transactions	AR Remittance from EDI Transactions
278	User34	Accounting	Accounts Receivable	Transactions	Calculate Commissions
279	User34	Accounting	Accounts Receivable	Transactions	Convert Prospect to Customer
280	User34	Accounting	Accounts Receivable	Transactions	Factor Cash Receipt
281	User34	Accounting	Accounts Receivable	Administration	Aging and Purge Zeroed Invoices
282	User34	Accounting	Accounts Receivable	Administration	Balance A/R Open Item Currency Exchange Amounts
283	User34	Accounting	Accounts Receivable	Administration	Credit Card Credit Form
284	User34	Accounting	Order Entry	Transactions	Apply Router Changes to Rate Tables
285	User34	Accounting	Order Entry	Transactions	Bill of Lading - Delete
286	User34	Accounting	Order Entry	Transactions	Clear EDI Invoice Selection File
287	User34	Accounting	Order Entry	Transactions	Generate EDI Invoices
288	User34	Accounting	Order Entry	Transactions	Invoicing
289	User34	Accounting	Order Entry	Transactions	Master Bill of Lading - Open
290	User34	Accounting	Purchasing	Transactions	Last Cost vs. Standard Cost
291	User34	Accounting	Purchasing	Transactions	PO Receipts by Container
292	User34	Accounting	Inventory	Transactions	Calculate Next Cycle Date
293	User34	Accounting	Inventory	Transactions	Inventory Transfers
294	User34	Accounting	Inventory	Transactions	Part Allocations
295	User34	Accounting	Payroll	Transactions	Journal Entries - Payroll Journal Entries
296	User34	Accounting	Payroll	Transactions	Manual/Void Check Processing - Manual Checks
297	User34	Accounting	Payroll	Administration	Employee Name Separation
298	User34	Accounting	Shop Floor Control	Transactions	Charge Material/Expenses (Standalone)
299	User34	Accounting	Shop Floor Control	Transactions	Edit Flexible Schedule
300	User34	Accounting	Shipping & Receiving	Transactions	PO Receipts by Container
Access Data

My "SOD Testing" sheet. Several cells are highlighted in red, and I've already run the code on this sheet, so all values are already filled in.

SOD Testing v2.xlsm
	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
1	List of Duties/Jobs	ACH Export	Aging and Purge Zeroed Invoices	AP Batch Entry - New	Apply Cust Values to Ship-To Record	Apply Router Changes to Rate Tables	AR Batches - Open	AR Remittance from EDI Transactions	Balance A/R Open Item Currency Exchange Amounts	Bill of Lading - Delete	BOM In Use	Calculate Commissions	Calculate Next Cycle Date	Calculate Workcenter MTD and YTD Hours	Charge Material/Expenses (Standalone)	Clear EDI Invoice Selection File	Clear Forecast Data	Convert Prospect to Customer	Create AP Invoice from Receipts	Create JE for Receipt of Container	Credit Card Credit Form	Daily Balancing (No Dollars)	Delete Credit Card Audit File	Delete Work Orders	Edit Flexible Schedule	Employee Name Separation	Factor Cash Receipt	Generate EDI Invoices	GL Batches In Use	GL Currency Conversion	Initialize Financial Statements	Inventory Master BIN Mass Update (c)	Inventory Transfers	Invoicing	Journal Entries - Delete	Journal Entries - Payroll Journal Entries	Last Cost vs. Standard Cost	Maintain Cause and Corrective Action	Maintain Engineering Change Control - Delete	Maintain Item Master User Titles (Lt/Bn)	Manual Checks	Manual/Void Check Processing - Manual Checks	Mass Replace Materials	Mass Replace Workcenters in Work Orders	Master Bill of Lading - Open	Moveable Dispatch Chart	Net Change Summary by Account	Part Allocations	PO Receipts by Container	Purchase Orders In Use	Purge Quality	Rate Table Copy	Rebuild Alternate Keys - Vendors	Refresh Inv Alt Cost from Latest Cost
2	ACH Export		0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0
3	Aging and Purge Zeroed Invoices	3		3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
4	AP Batch Entry - New	8	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Apply Cust Values to Ship-To Record	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
6	Apply Router Changes to Rate Tables	0	0	0	0		0	0	0	9	0	0	9	0	0	9	0	0	0	0	0	0	0	0	0	0	0	9	0	0	0	0	9	9	0	0	0	0	0	0	0	0	0	0	9	0	0	9	0	0	0	0	0	0
7	AR Batches - Open	0	0	0	0	4		4	0	4	0	4	0	0	0	4	0	4	0	0	0	0	0	0	0	0	4	4	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0
8	AR Remittance from EDI Transactions	0	0	0	0	4	4		0	4	0	4	0	0	0	4	0	4	0	0	0	0	0	0	0	0	4	4	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0
9	Balance A/R Open Item Currency Exchange Amounts	3	3	3	0	3	3	3		3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
10	Bill of Lading - Delete	0	0	0	0	9	0	0	0		0	0	9	0	0	9	0	0	0	0	0	0	0	0	0	0	0	9	0	0	0	0	9	9	0	0	0	0	0	0	0	0	0	0	9	0	0	9	0	0	0	0	0	0
11	BOM In Use	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
12	Calculate Commissions	0	0	0	0	4	4	4	0	4	0		0	0	0	4	0	4	0	0	0	0	0	0	0	0	4	4	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0
13	Calculate Next Cycle Date	0	0	0	0	19	0	0	0	19	0	0		0	0	19	0	0	0	0	0	0	0	0	0	0	0	19	0	0	0	0	19	19	0	0	0	0	0	0	0	0	0	0	19	0	0	19	0	0	0	0	0	0
14	Calculate Workcenter MTD and YTD Hours	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
15	Charge Material/Expenses (Standalone)	0	0	0	0	0	0	0	0	0	0	0	15	0		0	0	0	0	0	0	15	0	15	15	0	0	0	0	0	0	0	15	0	0	0	15	0	0	0	0	0	0	0	0	15	0	15	30	0	0	0	0	0
16	Clear EDI Invoice Selection File	0	0	0	0	9	0	0	0	9	0	0	9	0	0		0	0	0	0	0	0	0	0	0	0	0	9	0	0	0	0	9	9	0	0	0	0	0	0	0	0	0	0	9	0	0	9	0	0	0	0	0	0
17	Clear Forecast Data	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
18	Convert Prospect to Customer	0	0	0	0	4	4	4	0	4	0	4	0	0	0	4	0		0	0	0	0	0	0	0	0	4	4	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0
19	Create AP Invoice from Receipts	8	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0
20	Create JE for Receipt of Container	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3		3	3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
21	Credit Card Credit Form	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3		3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
22	Daily Balancing (No Dollars)	0	0	0	0	0	0	0	0	0	0	0	6	0	6	0	0	0	0	0	0		0	6	6	0	0	0	0	0	0	0	6	0	0	0	6	0	0	0	0	0	0	0	0	6	0	6	12	0	0	0	0	0
23	Delete Credit Card Audit File	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
24	Delete Work Orders	0	0	0	0	0	0	0	0	0	0	0	3	0	3	0	0	0	0	0	0	3	0		3	0	0	0	0	0	0	0	3	0	0	0	3	0	0	0	0	0	0	0	0	3	0	3	6	0	0	0	0	0
25	Edit Flexible Schedule	0	0	0	0	0	0	0	0	0	0	0	15	0	15	0	0	0	0	0	0	15	0	15		0	0	0	0	0	0	0	15	0	0	0	15	0	0	0	0	0	0	0	0	15	0	15	30	0	0	0	0	0
26	Employee Name Separation	4	4	4	0	4	4	4	4	4	0	4	4	0	4	4	0	4	4	4	4	4	0	0	4		4	4	4	4	0	0	4	4	4	4	4	4	4	0	4	4	0	0	4	4	4	4	8	0	0	0	4	0
27	Factor Cash Receipt	0	0	0	0	4	4	4	0	4	0	4	0	0	0	4	0	4	0	0	0	0	0	0	0	0		4	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0
28	Generate EDI Invoices	0	0	0	0	9	0	0	0	9	0	0	9	0	0	9	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	9	9	0	0	0	0	0	0	0	0	0	0	9	0	0	9	0	0	0	0	0	0
29	GL Batches In Use	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3		3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
30	GL Currency Conversion	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3		0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
31	Initialize Financial Statements	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1
32	Inventory Master BIN Mass Update (c)	0	0	0	0	0	0	0	0	0	0	0	2	0	2	0	0	0	0	0	0	2	0	0	2	0	0	0	0	0	0		2	0	0	0	2	0	0	0	0	0	0	0	0	2	0	2	4	2	0	0	0	0
33	Inventory Transfers	0	0	0	0	19	0	0	0	19	0	0	19	0	0	19	0	0	0	0	0	0	0	0	0	0	0	19	0	0	0	0		19	0	0	0	0	0	0	0	0	0	0	19	0	0	19	0	0	0	0	0	0
34	Invoicing	0	0	0	0	9	0	0	0	9	0	0	9	0	0	9	0	0	0	0	0	0	0	0	0	0	0	9	0	0	0	0	9		0	0	0	0	0	0	0	0	0	0	9	0	0	9	0	0	0	0	0	0
35	Journal Entries - Delete	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3	3	0	0	3	3		3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0	3	0
36	Journal Entries - Payroll Journal Entries	4	4	4	0	4	4	4	4	4	0	4	4	0	4	4	0	4	4	4	4	4	0	0	4	4	4	4	4	4	0	0	4	4	4		4	4	4	0	4	4	0	0	4	4	4	4	8	0	0	0	4	0
37	Last Cost vs. Standard Cost	0	0	0	0	0	0	0	0	0	0	0	8	0	8	0	0	0	0	0	0	8	0	8	8	0	0	0	0	0	0	0	8	0	0	0		0	0	0	0	0	0	0	0	8	0	8	16	0	0	0	0	0
38	Maintain Cause and Corrective Action	0	0	0	0	3	0	0	0	3	0	0	3	0	3	3	0	0	0	0	0	0	0	0	3	0	0	3	0	0	0	0	3	3	0	0	3		3	0	0	0	0	0	3	3	0	3	6	0	0	0	0	0
39	Maintain Engineering Change Control - Delete	0	0	0	0	3	0	0	0	3	0	0	3	0	3	3	0	0	0	0	0	0	0	0	3	0	0	3	0	0	0	0	3	3	0	0	3	3		0	0	0	0	0	3	3	0	3	6	0	0	0	0	0
40	Maintain Item Master User Titles (Lt/Bn)	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	2	1	1	1	1	1
41	Manual Checks	8	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0
42	Manual/Void Check Processing - Manual Checks	4	4	4	0	4	4	4	4	4	0	4	4	0	4	4	0	4	4	4	4	4	0	0	4	4	4	4	4	4	0	0	4	4	4	4	4	4	4	0	4		0	0	4	4	4	4	8	0	0	0	4	0
43	Mass Replace Materials	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	2	1	1	1	1	1
44	Mass Replace Workcenters in Work Orders	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	2	1	1	1	1	1
45	Master Bill of Lading - Open	0	0	0	0	9	0	0	0	9	0	0	9	0	0	9	0	0	0	0	0	0	0	0	0	0	0	9	0	0	0	0	9	9	0	0	0	0	0	0	0	0	0	0		0	0	9	0	0	0	0	0	0
46	Moveable Dispatch Chart	0	0	0	0	0	0	0	0	0	0	0	7	0	7	0	0	0	0	0	0	7	0	7	7	0	0	0	0	0	0	0	7	0	0	0	7	0	0	0	0	0	0	0	0		0	7	14	0	0	0	0	0
47	Net Change Summary by Account	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3		3	6	0	0	0	3	0
48	Part Allocations	0	0	0	0	19	0	0	0	19	0	0	19	0	0	19	0	0	0	0	0	0	0	0	0	0	0	19	0	0	0	0	19	19	0	0	0	0	0	0	0	0	0	0	19	0	0		0	0	0	0	0	0
49	PO Receipts by Container	0	0	0	0	0	0	0	0	0	0	0	21	0	21	0	0	0	0	0	0	21	0	21	21	0	0	0	0	0	0	0	21	0	0	0	21	0	0	0	0	0	0	0	0	21	0	21		0	0	0	0	0
50	Purchase Orders In Use	0	0	0	0	0	0	0	0	0	0	0	2	0	2	0	0	0	0	0	0	2	0	0	2	0	0	0	0	0	0	2	2	0	0	0	2	0	0	0	0	0	0	0	0	2	0	2	4		0	0	0	0
51	Purge Quality	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1		1	1	1
52	Rate Table Copy	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1		1	1
53	Rebuild Alternate Keys - Vendors	3	3	3	0	3	3	3	3	3	0	3	3	0	3	3	0	3	3	3	3	3	0	0	3	3	3	3	3	3	0	0	3	3	3	3	3	3	3	0	3	3	0	0	3	3	3	3	6	0	0	0		0
54	Refresh Inv Alt Cost from Latest Cost	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1
SOD Testing

Cell Formulas
Range	Formula
C1:BC1	C1	=TRANSPOSE(SORT(UNIQUE(tblData[Job Name])))
B2:B54	B2	=SORT(UNIQUE(tblData[Job Name]))
Dynamic array formulas.

My "SOD Notifications" sheet, with the results after running the code.

SOD Testing v2.xlsm
	A	B	C
1	User	Job Name 1	Job Name 2
2	User3	Calculate Next Cycle Date	Invoicing
3	User3	Calculate Next Cycle Date	Invoicing
4	User3	Calculate Next Cycle Date	Invoicing
5	User3	Calculate Next Cycle Date	Invoicing
6	User3	Calculate Next Cycle Date	Invoicing
7	User3	Calculate Next Cycle Date	Invoicing
8	User3	Calculate Next Cycle Date	Invoicing
9	User3	Calculate Next Cycle Date	Invoicing
10	User3	Calculate Next Cycle Date	Invoicing
11	User3	Calculate Next Cycle Date	Invoicing
12	User3	Calculate Next Cycle Date	Invoicing
13	User3	Calculate Next Cycle Date	Invoicing
14	User3	Calculate Next Cycle Date	Invoicing
15	User3	Calculate Next Cycle Date	Invoicing
16	User3	Calculate Next Cycle Date	Invoicing
17	User3	Calculate Next Cycle Date	Invoicing
18	User3	Calculate Next Cycle Date	Invoicing
19	User3	Calculate Next Cycle Date	Invoicing
20	User3	Calculate Next Cycle Date	Invoicing
21	User4	Edit Flexible Schedule	Moveable Dispatch Chart
22	User4	Edit Flexible Schedule	Moveable Dispatch Chart
23	User4	Edit Flexible Schedule	Moveable Dispatch Chart
24	User4	Edit Flexible Schedule	Moveable Dispatch Chart
25	User4	Edit Flexible Schedule	Moveable Dispatch Chart
26	User4	Edit Flexible Schedule	Moveable Dispatch Chart
27	User4	Edit Flexible Schedule	Moveable Dispatch Chart
28	User4	Edit Flexible Schedule	Moveable Dispatch Chart
29	User4	Edit Flexible Schedule	Moveable Dispatch Chart
30	User4	Edit Flexible Schedule	Moveable Dispatch Chart
31	User4	Edit Flexible Schedule	Moveable Dispatch Chart
32	User4	Edit Flexible Schedule	Moveable Dispatch Chart
33	User4	Edit Flexible Schedule	Moveable Dispatch Chart
34	User4	Edit Flexible Schedule	Moveable Dispatch Chart
35	User4	Edit Flexible Schedule	Moveable Dispatch Chart
36	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
37	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
38	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
39	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
40	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
41	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
42	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
43	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
44	User3	Generate EDI Invoices	Apply Router Changes to Rate Tables
45	User28	Manual/Void Check Processing - Manual Checks	PO Receipts by Container
46	User28	Manual/Void Check Processing - Manual Checks	PO Receipts by Container
47	User28	Manual/Void Check Processing - Manual Checks	PO Receipts by Container
48	User28	Manual/Void Check Processing - Manual Checks	PO Receipts by Container
SOD Notifications

 

[DanteAmor]

This is my first time using XL2BB, so hopefully I am doing this right.

It's perfect!

I am reviewing your code and I can see that you have this line:
If r.Interior.Color = vbRed Then blnNotify = True

That always slows down the process as you have to directly review the content of the cell.
Is there another way to perform that condition, that is, what determines that a cell has its red interior?

 

[DanteAmor]

With your example, the first code gets 47 records. The second code gets only 30 records . If you agree, I consider the first code as an example, to analyze it.

 

[Veritan]

The primary driver is whether or not it is acceptable for a single user to be able to perform 2 separate jobs. The whole point of this is for our internal audit department to know whether or not we are in compliance with SOX Separation of Duties (SOD) rules in the US. For example, it would potentially be unethical for a single person to be able to both post a journal entry as well as review that entry. SOX requires that those 2 functions be performed by 2 separate people in order to minimize the chances of fraud occurring.

I need a way for our auditors to be able to easily identify potentially conflicting jobs that doesn't require any major effort on their part. Highlighting cells in a certain color certainly is simple, but if it's impeding the execution speed, then maybe an alternative approach would be better.

An alternative way of identifying jobs that cannot be performed by the same person might be to have a table with a list of the jobs in one field, and the incompatible jobs in the second field. Perhaps having our audit department enter values into there and then having the code read that table into an array would be one way of speeding it up.

Ultimately, this file is intended for non-power users of Excel, so I need to keep it simple enough for them to be able to use effectively, while still automating the process enough so that they don't have to manually review each one of our thousands of employees

 

[Veritan]

With your example, the first code gets 47 records. The second code gets only 30 records .

Good catch I didn't see that. I checked the results manually, and my Version 2 code (the one with the arrays) is the one that is providing the correct results. I must have an error in my first one. Thanks for finding!

 

[DanteAmor]

An alternative way of identifying jobs that cannot be performed by the same person might be to have a table with a list of the jobs in one field, and the incompatible jobs in the second field. Perhaps having our audit department enter values into there and then having the code read that table into an array would be one way of speeding it up.

You work on that alternative and when you have it well defined you go back here and explain it.

At the end of the macro, the cells you require could be highlighted, but that can be done with an object and would be a single step. You wouldn't need to read cell by cell.

my Version 2 code (the one with the arrays) is the one that is providing the correct results

Okay, check out macro2.

 

[DanteAmor]

I have good news. We can keep the cells red.

I checked your code2 and the result has this problem:
At the end of the data you are repeating 3 records.

In columns E, F and G are the correct results.

The following code puts the results in cell E2 onwards. Maybe the code is more extensive, but it will certainly be faster. (And it works with the cells in red.)

Sub Check_Duties_Array_3()
  Dim a As Variant, b As Variant, c As Range
  Dim wsSod As Worksheet, wsData As Worksheet
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim f As Range, rngDuties As Range
  Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim cell As String
  Dim ky1 As Variant, ky2 As Variant, ky3 As Variant
  
  Set wsSod = Sheets("SOD Testing")
  Set wsData = Sheets("Access Data")

  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  lr = wsSod.Range("B" & Rows.Count).End(3).Row
  lc = wsSod.Cells(1, Columns.Count).End(1).Column
  Set rngDuties = wsSod.Range("C2", wsSod.Cells(lr, lc))
  
  Application.FindFormat.Clear
  Application.FindFormat.Interior.ColorIndex = 3
  Set f = rngDuties.Find("", rngDuties.Cells(1), xlFormulas, xlPart, xlByRows, xlNext, False, SearchFormat:=True)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      dic1(wsSod.Cells(f.Row, "B").Value & "|" & wsSod.Cells(1, f.Column).Value) = Empty
      Set f = rngDuties.Find("", f, xlValues, xlWhole, xlByRows, xlNext, False, SearchFormat:=True)
    Loop While f.Address <> cell
  End If
  Application.FindFormat.Clear
  
  a = wsData.ListObjects(1).DataBodyRange.Value2
  ReDim b(1 To UBound(a, 1), 1 To 3)

  For i = 1 To UBound(a, 1)
    dic2(a(i, 1)) = Empty
    dic3(a(i, 1) & "|" & a(i, 5)) = Empty
  Next
  
  For Each ky3 In dic2.keys     'users
    For Each ky1 In dic1.keys   'red cells
      If dic3.exists(ky3 & "|" & Split(ky1, "|")(0)) And _
         dic3.exists(ky3 & "|" & Split(ky1, "|")(1)) Then
        j = j + 1
        b(j, 1) = ky3
        b(j, 2) = Split(ky1, "|")(0)
        b(j, 3) = Split(ky1, "|")(1)
      End If
    Next
  Next
  
  Sheets("SOD Notifications").Range("E2").Resize(j, 3).Value = b
End Sub

Note:
I need to include the part of the counter intTotal = intTotal + 1, I am still working on it. But I wanted to give you this part of the code for you to review with your data.

 

[Veritan]

So far, this is working extremely well It's providing me with a perfect list of the users and the conflicting jobs for each cell in red. Hopefully we can figure out how to get a count of each job intersection. Once that's in place, this will be perfect. Thanks for all your help so far!