Find cells that contain repeated words within a string

[ollyhughes1982]

Hi,

Re the below screenshot, would anybody please be able to advise how to find and count cells in a particular column that contain repeated words. In this example, manually counting shows that the result should be 3. Cardiff parkrun, Newport parkrun and Sharpham Fields Road parkrun do not contain repeated words, the other 3 do. The words can be anywhere in the string, they don't have to be consecutive.

I've tried a few things, but nothing is working.

Thanks in advance.

Olly.

 

[Fluff]

You could use a helper column with

=LET(Txt," "&A2&" ",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt," ","")))-1),w,REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt," ","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt," ","~",Qty)),""),--(ROWS(w)<>ROWS(UNIQUE(w))))

 

[ollyhughes1982]

You could use a helper column with

=LET(Txt," "&A2&" ",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt," ","")))-1),w,REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt," ","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt," ","~",Qty)),""),--(ROWS(w)<>ROWS(UNIQUE(w))))

Thanks. That worked. I need to build this into my bigger, main spreadsheet and will check that it works in there. Will come back if any issues. Thanks again.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[ollyhughes1982]

Hi, Fluff. A minor thing: is there a way to have it so if the result is negative it shows as blank, rather than 0? Thanks.

 

[Fluff]

You should never really mix text & numbers in a column, as it can cause problems.

 

[ollyhughes1982]

You should never really mix text & numbers in a column, as it can cause problems.

OK, I just wanted it to be consistent with my other formulas. No problem. Thanks

 

[ollyhughes1982]

Hi again, Fluff. I am now incorporating the formula into my full spreadsheet and am struggling to get it to work. My range column is B, so changed from A to B in the formula. The helper column is now BG, rather than B. How would I amend it so that it works with this, please?

=LET(Txt," "&B2&" ",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt," ","")))-1),w,REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt," ","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt," ","~",Qty)),""),r,--(ROWS(w)<>ROWS(UNIQUE(w))),IF(r=0,"",r))

Thanks.

 

[ollyhughes1982]

Here is a one drive link to the proper full file. It is a large file, so can take a while to open.

Link: parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING 3.xlsx

Thanks again.

 

[ollyhughes1982]

Here are some screenshots of the layout, if you struggle to open the file.

Event List, in column B:

Where I need the formula, in column BG:

Thanks again.