SOLVED

Comparing data & automatic adding of classification

%3CLINGO-SUB%20id%3D%22lingo-sub-2526950%22%20slang%3D%22en-US%22%3EComparing%20data%20%26amp%3B%20automatic%20adding%20of%20classification%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526950%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help%20me%20out%20here.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20I'm%20working%20has%20about%205000%20rows%20with%20various%20comments.%20I%20have%20to%20classify%20these%20comments%20in%204%20categories%20and%20for%20one%20category%20I%20have%20to%20be%20even%20more%20specific.%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EComment%3A%20%22Out%20of%20time%22%20-%20means%20the%20guy%20didn't%20have%20enough%20time%20to%20deliver%20a%20parcel.%20That%20would%20be%20classified%20as%20COI%2FLMD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EComment%3A%20%22Weather%22%20-%20would%20be%20OTH%20(other)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20now%20is%2C%20is%20it%20somehow%20possible%20to%20create%20something%20that%20populates%20the%20classification%20for%20me%2C%20after%20checking%20if%20the%20comment%20fits%20in%20this%20spectrum%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elike%3C%2FP%3E%3CP%3E%22%3DIF(outoftime%3C%2FP%3E%3CP%3Ethen%20COI%2FLMD%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20would%20be%20java%2C%20but%20is%20it%20possible%20to%20create%20something%20like%20that%20for%20xls%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ekind%20regards%3C%2FP%3E%3CP%3ERaph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2526950%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2527143%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20data%20%26amp%3B%20automatic%20adding%20of%20classification%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527143%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098365%22%20target%3D%22_blank%22%3E%40urgettingbloodeagled%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20mapping%20table%20(shown%20on%20the%20same%20sheet%20below%20but%20it%20can%20be%20on%20a%20different%20sheet)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20363px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294239iE986E57BDE1FC690%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20in%26nbsp%3B%3CSTRONG%3EB3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(A3%2C%20D%243%3AD%246%2C%20E%243%3AE%246%2C%20%22No%20match%22%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECorresponding%20sample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2527179%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20data%20%26amp%3B%20automatic%20adding%20of%20classification%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527179%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20quick%20turn%20around!%20your%20help%20is%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2527384%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20data%20%26amp%3B%20automatic%20adding%20of%20classification%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527384%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome.%20Any%20issue%20let%20me%20know%20%26amp%3B%20please%20upload%20a%20dummy%20representative%20workbook%3CBR%20%2F%3EOtherwise%20if%20problem%20solved%2C%20somewhere%20on%20each%20reply%20there's%20a%20link%20to%20mark%20as%20Best%20reponse%20%3D%26gt%3B%20Can%20help%20those%20who%20search%20-%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

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?

 

like

"=IF(outoftime

then COI/LMD"

 

I know that would be java, but is it possible to create something like that for xls?

 

kind regards

Raph

6 Replies
best response confirmed by urgettingbloodeagled (New Contributor)
Solution

Hi @urgettingbloodeagled 

 

Create a mapping table (shown on the same sheet below but it can be on a different sheet)

Demo.png

 

Formula in B3:

=XLOOKUP(A3, D$3:D$6, E$3:E$6, "No match", 0)

Corresponding sample attached

Thanks for the quick turn around! your help is appreciated

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

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!

 

kind regards

 

urgettingbloodeagled

Hi @urgettingbloodeagled 

 

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

@L z. 

 

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.

 

thanks !

 

have a nice weekend

 

kind regards

 

urgettingbloodeagled