Repeated word count in sentence

[Areif]

Hello all !!

Hope all are safe and doing well , i got stuck at finding the repeated words in sentence.
hope here i get the solution... reference sheet is attached

Thanks in advanced

 

[bosco_yip]

In D3, enter array (CSE) formula :

=TEXTJOIN(", ",1,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER(B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]"))

 

[Areif]

Thanks for reply , but getting #NAME? error, please suggest solution.

 

[bosco_yip]

Thanks for reply , but getting #NAME? error, please suggest solution.

I think your excel version do not have TEXTJOIN function.

Then, try this helper columns ways,

1] In "Helper" E3, formula copied right until blank :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(LOWER($B3),".","")," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]["&COLUMN(A1)&"]"),"")

2] In "Result" D3, enter formula :

=TRIM(E3&" "&F3&" "&G3&" "&H3&" "&I3&" "&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3&" "&P3)

 

[Areif]

Thanks for your kind support , its working fine now
Kudos to team Chandoo..