Find cells that contain alliteration

[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 alliteration. In this example, manually counting shows that the result should be 6. Cardiff parkrun, Newport parkrun and Sharpham Fields Road parkrun do not contain alliteration, the other 6 do.

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

Thanks in advance.

Olly.

 

[ollyhughes1982]

Ah, apologies. Thanks

 

[lrobbo314]

@Fluff, I was trying to recreate your formula to try and understand it. Ended up with the formula below which was a good bit shorter. Thought it might interest you. Again, very cool solution. I can add this skill to my toolbox thanks to you. Cheers.

=LET(x,MID(A2,FIND("~",SUBSTITUTE(" "&A2&" "," ","~",SEQUENCE((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))+1))),1),y,INDEX(x,SEQUENCE(COUNTA(x),,2)),SUM(IFERROR((x=y)+0,0)))

 

[ollyhughes1982]

Hi both. I was just about to message again. A minor thing: is there a way to have it so if the result is negative it shows as blank, rather than 0? Thanks.

 

[lrobbo314]

How about

=LET(x,MID(A2,FIND("~",SUBSTITUTE(" "&A2&" "," ","~",SEQUENCE((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))+1))),1),y,INDEX(x,SEQUENCE(COUNTA(x),,2)),s,SUM(IFERROR((x=y)+0,0)),IF(s=0,"",s))

 

[ollyhughes1982]

How about

=LET(x,MID(A2,FIND("~",SUBSTITUTE(" "&A2&" "," ","~",SEQUENCE((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))+1))),1),y,INDEX(x,SEQUENCE(COUNTA(x),,2)),s,SUM(IFERROR((x=y)+0,0)),IF(s=0,"",s))

Thanks. That did the trick

 

[ollyhughes1982]

Hi again both. 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 BF, rather than B. How would I amend it so that it works with this, please?

=LET(x,MID(B2,FIND("~",SUBSTITUTE(" "&B2&" "," ","~",SEQUENCE((LEN(B2)-LEN(SUBSTITUTE(B2," ","")))+1))),1),y,INDEX(x,SEQUENCE(COUNTA(x),,2)),s,SUM(IFERROR((x=y)+0,0)),IF(s=0,"",s))

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, both

 

[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 BF:

Thanks again.

 

[Fluff]

Judging from the image you need to change B2 to B3

 

[ollyhughes1982]

Oh dear, oh dear, that really is a dumb one by me. Apologies!