All,
I was looking for assistance or some determination if two formulas I wrote for work can be combined into a single formula. See attached spreadsheet cleansed of production data (it's work related) and the two formula I'd like help with (also in fields Y and Z in attached spreadsheet)
1) =IF(OR(H2="OperatorRejectedClose", H2="Order submit - Failed"), "Hard Touch", IF(OR(H2="AutoReject", H2="File download - Rejected"), "Upfront Rejection", IF(H2 = "Order submit - Completed","Yield"," ")))
2) ={IF(Y2="Yield",MATCH(A2&{"*fication*"},A:A&H:H,0), " ")}
Here's the overall idea - it's a spreadsheet with log data. If formula one finds "File download - Rejected", "Order submit - Failed", or "Order Submit - Completed" in field H, it returns "Upfront Rejection, "Hard Touch", or "Yield", respectively. Now, those line items that return "Yield' need to be broken down into "Soft Touch" or "Frictionless" (or alternatively, "Yield - Soft Touch" and "Yield - Frictionless which was the original goal writing these formulas) which is the point of the second array formula. The data is kinda wonky which is why such a complex set of formulas is needed (department didn't have reporting in mind when they developed the tool).
Please see attached spreadsheet - I know this is actually probably not the best way to do this (maybe VBA would help? I don't have any idea how to do this so that code would be SOOOOO helpful).
I've put the "Logic" or my intention in the merged cells below and highlighted an example of each case for the second formula (green My hope is combine these two into a single formula. It's a lot - I know, and I've been as detailed as possible both here and in my example spreadsheet in hopes someone will understand and tell me if there is a better way to accomplish this search. Currently, because of the way the second formula works and the actual number of records in a set of data, the second formula really takes time to go through several thousand lines of records to return my result. Any help would be greatly appreciated. Let me know if this isn't clear or if more clarification is needed. I appreciate you all!!!
By the way, all this data was made up for the example so nobody has to worry about sensitive data or anything like that. Thank you again.
Bookmarks