Forum Discussion
Formula Help
You might try putting your LHS conditions into a table and then using a formula to see if the values in E4, F4, and G4 exist in the table. Then, adding additional entries is just a matter of adding items to the table. Here is a description of how the formula is being used for a multiple condition test, so hopefully you will be able to follow it:
http://xldynamic.com/source/xld.SUMPRODUCT.html
As mathetes has also already said, this is not ideal. Ideally, whatever application or software you are using on the front end would have data validation so that the input was standard or would have some more reliable means of identifying LHS situations. But, if that's not an option, perhaps this will be somewhat helpful.
JMB17 gave us a great start with his LHS spreadsheet.
I've extended the table and added what I call a "cascading data validation system" in the attached. This does require the most current version of Excel because it makes extensive use of the FILTER and UNIQUE functions.
So Carl_61 there's now a simple formula that identifies which conditions are LHS based on the table at the far left of the sheet.
- Thank you sir, I can say it looks good but I am not clear on what is going on here. The question is how do I apply this against my report? I get a work order report with sometimes up to 300 work orders, give or take, and I need a way to run a solution against the report whereby each work order is checked via the solution we come up with. I'm not apposed to a VBA Code solution as well. What's your thoughts on how I process this solution against the report?
- No. Work orders are entered into the system when called into management and the comments are typed in how the resident speaks them. Also, residents are remotely able to enter work order requests thru an app. The only unstable entries are those which are entered into the the comments field when the work order is created. There is no way to standardize the entry of comments. The only standard is the selection of the Work Order Category and the Sub-category which gives a path for determining what possible comments could match the categories .
- I've requested a complete list of both selectable Work Order Categories and Sub-categories from the people that create the work orders. We obviously know that there is no standardization for what is entered in the Work Order Description remarks cell so this is why we need to have a way looking for key words that may lead us to a possible "LHS" situation.
