Compare Sentence to sentence in two sets of data

%3CLINGO-SUB%20id%3D%22lingo-sub-934694%22%20slang%3D%22en-US%22%3ECompare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934694%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20two%20sets%20of%20details%20regarding%20a%20same%20item%2Fproduct.%20Need%20to%20know%20how%20can%20I%20compare%20them%20sentence%20to%20sentence%20or%20word%20to%20word.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEg%3A%3CBR%20%2F%3EColumn%201%20%3A%20John%20is%20now%2015%20years%20old%2C%20i%20had%20met%20him%20few%20years%20back%20when%20he%20was%20a%205%20year%20old.%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%202%3A%20Mark's%20son%20John%20is%2015%20years%20old%2C%20I%20had%20met%20him%20few%20years%20back%20when%20he%20was%205%20a%20year%20old%2C%20today%20he%20has%20grown%20up%20into%20an%20all%20rounder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20such%20details%20for%201000%20of%20rows%3B%20Need%20help%20in%20the%20following%3C%2FP%3E%3CP%3E1.%20How%20to%20match%20if%20C1%20is%20sub%20set%20of%20C2%20using%20string%20match%20or%20vice%20versa%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20how%20to%20check%20%25%20of%20matching%20of%20C1%20on%20C2.%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20Is%20it%20possible%20if%20there%20is%2Fare%20unrelated%20words%20in%20between%20of%20a%20sentence%20and%20still%20can%20be%20matched%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20is%20much%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-934694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934753%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934753%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%2F431665%22%20target%3D%22_blank%22%3E%40nitinprabhu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20possible%20with%20native%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-935003%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-935003%22%20slang%3D%22en-US%22%3EAs%20Wyn%20has%20already%20said%2C%20Excel%20would%20not%20be%20the%20right%20tool%20for%20that.%20In%20fact%2C%20I%20can't%20think%20of%20any%20normal%20office%20software%20product%2C%20off%20the%20shelf%2C%20that%20could%20do%20that.%3CBR%20%2F%3EHOWEVER%2C%20if%20all%20of%20your%20entries%20contain%20similar%20kinds%20of%20entries%20(and%20if%20this%20is%20something%20you're%20going%20to%20be%20doing%20for%20the%20future)...I%20CAN%20conceive%20of%20using%20Excel%20if%20you%20were%20to%20re-think%20this%20as%20a%20database%20rather%20than%20simply%20a%20place%20to%20store%20casual%20notes.%3CBR%20%2F%3EIF%2C%20for%20example%2C%20you%20always%20record%20DATEFIRSTMET%2C%20BIRTHDAY%20(or%20some%20basic%20reference%20that%20would%20allow%20you%20to%20calculate%20age%20today%20or%20at%20other%20points%20in%20time)%2C%20GRADDATE%2C%20etc.%2C%20and%2C%20importantly%2C%20have%20standardized%20answers%20(a%20range%20of%20them%20could%20be%20created)%20for%20entries%20in%20such%20fields%20as%20PRETEEN%20PERS(onality)%2C%20TEEN%20PERS(onality)...into%20which%20such%20descriptons%20as%20%22rounder%22%2C%22shy%22%2C%22bookish%22%20or%20whatever....%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20you%20get%20the%20idea%3F%20This%20sounds%20like%20you're%20a%20counselor%20in%20some%20setting....%3CBR%20%2F%3E%3CBR%20%2F%3EI%20realize%20that%20all%20I'm%20doing%20is%20scratching%20the%20surface%20of%20how%20such%20a%20database%20could%20be%20created%2C%20and%20you'd%20always%20have%20to%20have%20a%20column%20or%20two%20to%20allow%20you%20to%20enter%20%22non-standard%22%20observations%2C%20or%20a%20note%20to%20self%20on%20%22this%20is%20what%20we%20need%20to%20talk%20about%20next%20time%20we%20meet%22....%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20you%20have%20done%20so%20far%2C%20though%2C%20is%20to%20use%20Excel%20simply%20as%20a%20note%20taking%20device%20which%20happens%20to%20be%20in%2Fon%20a%20computer%3B%20you've%20not%20used%20EXCEL%20for%20the%20purposes%20it%20actually%20serves.%20To%20do%20the%20latter%2C%20you%20need%20to%20re-think%20the%20way%20you%20keep%20those%20records.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-940118%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-940118%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431665%22%20target%3D%22_blank%22%3E%40nitinprabhu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suddenly%20realised%20you%20may%20be%20able%20to%20do%20part%20of%20this%20using%20Excel%20Power%20Query%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(if%20you%20have%20Excel%202016%20or%20later%20this%20demo%20file%20should%20work)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20used%20Power%20Query%20before%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20only%20compares%20the%20number%20of%20words%20that%20match%20list%201%2C%20and%20doesn't%20look%20at%20order%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F145169iEB4610015E30CA8D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768073%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BThank%20you%20for%20sharing%20%22Sentence%20Comparer.xlsx%22%20I%20found%20it%20very%20helpful.%20I%20noticed%20that%20when%20using%20it%20with%20my%20data%20of%201053%20sentences%20it%20switches%20the%20order%20of%20comparisons%20leading%20to%20comparing%20sentences%20from%20different%20lines%20that%20usually%20results%20in%200%20matches.%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestions%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EAziz%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225764i94BFCB9E316F5A77%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.png%22%20alt%3D%22Untitled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768293%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20Sentence%20to%20sentence%20in%20two%20sets%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768293%22%20slang%3D%22en-US%22%3EYou%20might%20check%20into%20the%20fuzzy%20lookup%20add-in.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi I have two sets of details regarding a same item/product. Need to know how can I compare them sentence to sentence or word to word. 

 

Eg:
Column 1 : John is now 15 years old, i had met him few years back when he was a 5 year old. 

Column 2: Mark's son John is 15 years old, I had met him few years back when he was 5 a year old, today he has grown up into an all rounder.

 

I have such details for 1000 of rows; Need help in the following

1. How to match if C1 is sub set of C2 using string match or vice versa 

2. how to check % of matching of C1 on C2. 

3. Is it possible if there is/are unrelated words in between of a sentence and still can be matched? 

 

Your help is much appreciated. 

 

Thanks 

4 Replies

Hi @nitinprabhu 

 

That's not possible with native Excel

As Wyn has already said, Excel would not be the right tool for that. In fact, I can't think of any normal office software product, off the shelf, that could do that.
HOWEVER, if all of your entries contain similar kinds of entries (and if this is something you're going to be doing for the future)...I CAN conceive of using Excel if you were to re-think this as a database rather than simply a place to store casual notes.
IF, for example, you always record DATEFIRSTMET, BIRTHDAY (or some basic reference that would allow you to calculate age today or at other points in time), GRADDATE, etc., and, importantly, have standardized answers (a range of them could be created) for entries in such fields as PRETEEN PERS(onality), TEEN PERS(onality)...into which such descriptons as "rounder","shy","bookish" or whatever....

Do you get the idea? This sounds like you're a counselor in some setting....

I realize that all I'm doing is scratching the surface of how such a database could be created, and you'd always have to have a column or two to allow you to enter "non-standard" observations, or a note to self on "this is what we need to talk about next time we meet"....

What you have done so far, though, is to use Excel simply as a note taking device which happens to be in/on a computer; you've not used EXCEL for the purposes it actually serves. To do the latter, you need to re-think the way you keep those records.

@nitinprabhu 

 

I suddenly realised you may be able to do part of this using Excel Power Query

 

(if you have Excel 2016 or later this demo file should work)

 

Have you used Power Query before?

 

It only compares the number of words that match list 1, and doesn't look at order

 

image.png

 

 

@Wyn Hopkins Thank you for sharing "Sentence Comparer.xlsx" I found it very helpful. I noticed that when using it with my data of 1053 sentences it switches the order of comparisons leading to comparing sentences from different lines that usually results in 0 matches. 

Do you have any suggestions? 

 

Thank you

Aziz

 

Untitled.png