VBA over groups of data in a table

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
132
Hello,

I have a table of data related to an invoice approval workflow where I would like to evaluate each invoice (request key) for the appropriate approver. Unfortunately, the workflow software doesn't store the final approver in a field, so we have to apply logic to the workflow data to extract the approver in an automated fashion.

Below is a detailed example of the data that we would be evaluating.

For each unique request key, I would like to evaluate the following cases and return the appropriate name (could be placed in a new column next to the data for either the whole set, or just the first line, for ease of extraction with an excel formula). The data will be presorted so that the request keys will be grouped.

1) If Status Upon Step Completion for Step "Approval Level 1" = "Auto Approved" return Requesting Employee
2) For the last step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" return Employee Assigned to Step

My full sample of data is thousands of lines long and includes some items that would support the logic above (such as "Approved" Statuses where the Role is actually "Invoice Processor")

Example table:
ABCDEFG
1Request KeyRequesting EmployeeStep SequenceStepStatus Upon Step CompletionEmployee Assigned to StepRole
21113Gadot, Gal1Payment Request SubmittedSubmittedSystem
31113Gadot, Gal2Cost Object ApprovalAuto ApprovedSystemCost Object Approver
41113Gadot, Gal3Approval Level 1Auto ApprovedSystemInvoice Approver
51113Gadot, Gal4Approval Level 2Auto ApprovedSystemInvoice Approver
61113Gadot, Gal5Approval Level 3Auto ApprovedSystemInvoice Approver
71113Gadot, Gal6Approval Level 4Auto ApprovedSystemInvoice Approver
81113Gadot, Gal7Approval Level 5Auto ApprovedSystemInvoice Approver
91113Gadot, Gal8Approval Level 6Auto ApprovedSystemInvoice Approver
101113Gadot, Gal9Back Office ApprovalApprovedDoe, JohnInvoice Processor
111113Gadot, Gal10Vendor ApprovalAuto ApprovedSystemInvoice Vendor Manager
121113Gadot, Gal11Prepayment ValidationPending ValidationSystemSystem
131113Gadot, Gal12Pending PaymentExtractedSystemSystem
141113Gadot, Gal13Payment Accounting ExtractSystem
151019Saget, Bob2Cost Object ApprovalAuto ApprovedSystemCost Object Approver
161019Saget, Bob3Approval Level 1ApprovedStern, HowardInvoice Approver
171019Saget, Bob4Approval Level 2ApprovedCarrey, JimInvoice Approver
181019Saget, Bob5Approval Level 3Auto ApprovedSystemInvoice Approver
191019Saget, Bob6Approval Level 4Auto ApprovedSystemInvoice Approver
201019Saget, Bob7Approval Level 5Auto ApprovedSystemInvoice Approver
211019Saget, Bob8Approval Level 6Auto ApprovedSystemInvoice Approver
221019Saget, Bob9Back Office ApprovalApprovedDoe, JohnInvoice Processor
231019Saget, Bob10Vendor ApprovalAuto ApprovedSystemInvoice Vendor Manager
241019Saget, Bob11Prepayment ValidationPending ValidationSystemSystem
251019Saget, Bob12Pending PaymentExtractedSystemSystem
261019Saget, Bob13Payment Accounting ExtractSystem
271194Griffin, Peter1Payment Request SubmittedSubmittedSystem
281194Griffin, Peter2Cost Object ApprovalAuto ApprovedSystemCost Object Approver
291194Griffin, Peter3Approval Level 1ApprovedStern, HowardInvoice Approver
301194Griffin, Peter4Approval Level 2Auto ApprovedSystemInvoice Approver
311194Griffin, Peter5Approval Level 3Auto ApprovedSystemInvoice Approver
321194Griffin, Peter6Approval Level 4Auto ApprovedSystemInvoice Approver
331194Griffin, Peter7Approval Level 5Auto ApprovedSystemInvoice Approver
341194Griffin, Peter8Approval Level 6Auto ApprovedSystemInvoice Approver
351194Griffin, Peter9Back Office ApprovalApprovedDoe, JohnInvoice Processor
361194Griffin, Peter10Vendor ApprovalAuto ApprovedSystemInvoice Vendor Manager
371194Griffin, Peter11Prepayment ValidationPending ValidationSystemSystem
381194Griffin, Peter12Pending PaymentExtractedSystemSystem
391194Griffin, Peter13Payment Accounting ExtractSystem
401191Urkel, Steve1Payment Request SubmittedSubmittedSystem
411191Urkel, Steve2Cost Object ApprovalAuto ApprovedSystemCost Object Approver
421191Urkel, Steve3Approval Level 1Auto ApprovedSystemInvoice Approver
431191Urkel, Steve4Approval Level 2Auto ApprovedSystemInvoice Approver
441191Urkel, Steve5Approval Level 3Auto ApprovedSystemInvoice Approver
451191Urkel, Steve6Approval Level 4Auto ApprovedSystemInvoice Approver
461191Urkel, Steve7Approval Level 5Auto ApprovedSystemInvoice Approver
471191Urkel, Steve8Approval Level 6Auto ApprovedSystemInvoice Approver
481191Urkel, Steve9Back Office ApprovalApprovedDoe, JohnInvoice Processor
491191Urkel, Steve10Vendor ApprovalAuto ApprovedSystemInvoice Vendor Manager
501191Urkel, Steve11Prepayment ValidationPending ValidationSystemSystem
511191Urkel, Steve12Pending PaymentExtractedSystemSystem
521191Urkel, Steve13Payment Accounting ExtractSystem

<tbody>
</tbody>

Example results:
Request Key 1113 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Gadot, Gal" is returned
Request Key 1019 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 2", therefore "Carrey, Jim" is returned
Request Key 1194 - 1) Fails. 2) The last Step where Status Upon Step Completion = "Approved" and Role = "Invoice Approver" is "Approval Level 1", therefore "Stern, Howard" is returned
Request Key 1191 - 1) For Step "Approval Level 1" the Status Upon Step Completion = "Auto Approved", therefore "Urkel, Steve" is returned

For me, the most challenging piece of code here is evaluating each of the groups (unique request keys) against the cases, and not creating the code for evaluating the case statements. If I wasn't clear on the logic for the cases being evaluated, but you understand how the groups might be isolated and evaluated, your insight there would be much appreciated!

Thanks in advance,
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
132
I've used the range.areas property in a loop macro before, but that data was already isolated into groupings with blank spaces in between. That's not really necessary here, but I guess I could do that if it would make evaluating the groups easier? Any thoughts?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,290
Members
418,186
Latest member
mrahim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top