Forum Discussion
Formula Help
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.
- 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 .
First, Carl, you don't need to send each of us a reply separately. You can reply to multiple people here with one message, as I've done above. Just type the "@" symbol and the website itself will supply a list of others who are in the same thread.
Anyway, back to the issue at hand. Let me ask, since you can't change the raw data entry process, at which point does all this get put into Excel? How does that happen?
It just seems that worst case, you still would be better off NOT trying to come up with your own artificial intelligence formula that would take random text entries and make sense of them....maybe you need to hire a separate person to go down that long list and, using the table that JMB17 and I have given you, create a clean set of data points on each row, with a reliable formula, such as we've given you, to identify the priority items.
In the absence of that, you (or somebody) will still need to go through the list manually to make sure that the incomplete and unreliable formula --which is the best you and we will be able to do anyway -- hasn't missed anything. Basically, it's the process itself that needs to be made reliable.
- 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.