Forum Discussion

pinkyswear's avatar
pinkyswear
Copper Contributor
Aug 31, 2023

Text Comparison

Hi! I have two text columns and I need to compare them based on their similarity The first column contains main keywords, the second column contains two lines with the terms and similar cases. Pleas...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 02, 2023

pinkyswear 

I went with a solution using MAP:

 

=LET(
    SplitTerms, LAMBDA(ArrA, ArrB,
        LET(
            TermsA, TEXTSPLIT(ArrA, , ", "),
            TermsB, TEXTSPLIT(ArrB, , ", "),
            AllTerms, VSTACK(TermsA, TermsB),
            matches, COUNT(XMATCH(TermsA, TermsB)),
            denom, COUNTA(UNIQUE(AllTerms)),
            matches / denom
        )
    ),
    MAP(ListA, ListB, SplitTerms)
)
  • pinkyswear's avatar
    pinkyswear
    Copper Contributor
    Sep 06, 2023

    Patrick2788 and PeterBartholomew1 

     

    Hi! Thank you very much for your reply!

     

    Unfortunately, it's giving me the following error: "The first argument of LET must be a valid name."

     

    Please let me know if I should do some particular changes in the formula.

    I am comparing cells C2 and G2.

     

    A more particular example of the table would be provided further:

     

    Thank you in advance! Appreciate your help!

     

    flower_shCategory: flower_sh

    Keywords: tree care service, tree trimming, tree pruning, tree thinning, tree care, canopy balancing
    architec_servCategory: architec_serv

    Keywords: interior designer, residential design, commercial design, interior design services, project management service
    flower_sh ,home_stCategory: home_st

    Keywords: custom made fire pits, state of the art equipment, laser cutting technologies, premium quality cuts, stainless steel
    home_st Category: flower_sh

    Keywords: lawn care, affordable prices, quality service, mowing, weed removal, grass blowing, snow-blowing

Resources