Summarizing validation results into 1 column

I have approximately 5 to 6 data validation points I am utilizing to review invoicing for accuracy. I am stuck on how I can capture all the reasons for rejecting said invoice at this point. Right now I am only capturing the first one but when reviewing the data validation if the 1st reason is addressed the invoice will just reject for the 2nd 'not listed reason' and so on.

Below is an example of the validation points utilized to reject an invoice and would like the "Pay" column to just say approve, reject.. then the reasons provided in the comments column.

Thanks

Capture.PNG

Sample.xlsx

Tips and Tricks

Accepted answers

rzdodson

@Nickdye05 created an iterative macro for you that automates email creation for EDI tickets where there are rejection reasons.

The first section of the macro is some simple data preparation where I am generating a RecordID for each record in the data set, removing any records where there isn't a value in the EDI Ticket field (simplifying your Formula logic in your OP).

This section is what the iterative macro looks like under the hood. What is occurring in this section is finding the EDI tickets where there is a rejection reason for it, then dynamically building out a custom email message to send to that user who generated the ticket.

What you would need for future iterations of this workflow are EDI Ticket Creator, Recipient, or other related fields so that those who initiated the ticket and/or need to be aware of the traffic around it receive the same email when we have errors in ticket creation.

This workflow only takes into consideration if the ticket has at least one rejection reason. If there are instances where we have multiple rejection reasons to one ticket, and we want a composed email with all of the various rejection reasons, let me know. Will require some minor rework on the attached workflow.

Hope this helps!

Solution.yxzp

All comments

rzdodson

@Nickdye05 I think I can help you out here. When able, would you mind posting a dummy data set to help with solutioning?

In the interim, I am thinking that you create separate columns for each validation check, personally. Doing so will enable us to transpose the data set, then develop an iterative macro that checks if there are one or more instances where we have a bad invoice, consolidate the various reasons in to one field, and then output an invoice rejection message with something like: "[Insert Invoice Number] has been rejected due to the following reasons: [Reason1]. [Reason2], [Reason4], and [Reason 5]".

DanielG

@Nickdye05 - i mocked up a very basic example of what I would do. Not sure it will work for your exact needs but hopefully it will at least give you some ideas.

RejectSample.yxmd

Nickdye05

@rzdodson thank you for your response. I uploaded an abbreviated sample file to my OP. I do already have columns dedicated to the validation (highlighted in blue) within the sample. Thank you.

rzdodson

@Nickdye05 this should be pretty straightforward - will have a solution for you soon.

I did notice that Terminal_Prep is a blue category (re: should be a part of your data validation in your Excel), but it is not listed in the Formula tool that was in your PNG file. Are there any special parameters you need incorporated for the Terminal_Prep field?

Nickdye05

@rzdodson my apologies it plays into the terminal validation comparing that with the state creates that validation point.

rzdodson

Solution.yxzp

caltang

High quality solution with proper explanation and documentation in the annotations to boot. Kudos to you @rzdodson !

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235