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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 02, 2023

pinkyswear 

I failed to understand the layout of your data (the role of 'terms' and 'similar cases') so tackled a slightly different problem.

I am using 365 so I used FILTER to determine the union and intersection counts using the Lambda function

= LET(
    combined, TEXTJOIN(",", , sets),
    split,    TRIM(TEXTSPLIT(combined, ",")),
    union,    UNIQUE(split, TRUE),
    unique,   UNIQUE(split, TRUE, 1),
    error,    AND(ISERROR(unique)),
    1 - IF(error, 1, COUNTA(unique) / COUNTA(union))
  )

At least I have now learnt the term 'Jaccard similarity'!

Resources