Jul 08 2021 12:36 AM
Jul 08 2021 12:36 AM
I hope you can help me out here.
The file I'm working has about 5000 rows with various comments. I have to classify these comments in 4 categories and for one category I have to be even more specific. example.
Comment: "Out of time" - means the guy didn't have enough time to deliver a parcel. That would be classified as COI/LMD
Comment: "Weather" - would be OTH (other)
My question now is, is it somehow possible to create something that populates the classification for me, after checking if the comment fits in this spectrum?
I know that would be java, but is it possible to create something like that for xls?
Jul 08 2021 01:38 AMSolution
Create a mapping table (shown on the same sheet below but it can be on a different sheet)
Formula in B3:
=XLOOKUP(A3, D$3:D$6, E$3:E$6, "No match", 0)
Corresponding sample attached
Jul 08 2021 02:49 AM - edited Jul 08 2021 02:56 AM
You're welcome. Any issue let me know & please upload a dummy representative workbook
Otherwise if problem solved, somewhere on each reply there's a link to mark as Best reponse => Can help those who search - Thanks
Jul 16 2021 04:53 AM
HI @L z. ,
thanks again for your help, but I need to pick your brain once more.
Attached you see a dummy example for the issue. As you can see, I have multiple comments with the same context, only the date is various. My question now is, is it possible to set up a auto detect?
By that I mean, in the master file with all comments and classifications I have instead of
Misdelivered, delivered properly 10th March - only Misdelivered, delivered properly* or even shorter; Misdelivered, delivered* and when I ran the look up that is detects the classification for the "long comment" in a shorter version.
I hope you understand my thinking process here.
thanks in advance, for even trying!
Jul 16 2021 08:06 AM
Don't get me wrong but this has nothing to do with the problem you initialy raised + you have no classification
I carefully looked at what you submitted and tried to put something together (see attached file) to help you but I'm not sure at all this does what you want - Hopefully this will give you ideas...
And a recommendation if I may : work with Tables as doing this kind of things of > 1 Million of rows is expensive while a Table limits the range to the actual number of rows you use. One of the big advantages of Tables is they auto-resize as you add data. In other words they offer dynamic ranges
All the best
Jul 16 2021 08:11 AM
thanks again for the quick answer. It's not the initial issue, but is somewhat connected. If i should create a 2nd thread for it, i will gladly do it.
the classification would be on the 2nd sheet. maybe i explained it poorly. i'm ooo and don't have xls on my personal pc. ( for obv reasons haha). I will give a shot on monday.
have a nice weekend