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.

 

[lrobbo314]

A way using VBA.

Function ALLITERATION(r As Range)
Dim AR() As Variant:        AR = r.Value2
Dim tot As Integer:         tot = 0
Dim SP() As String
Dim tmp As String
Dim b As Boolean

For i = 1 To UBound(AR)
    SP = Split(AR(i, 1), " ")
    tmp = Left(SP(0), 1)
    b = False
    For j = 1 To UBound(SP)
        If UCase(tmp) = UCase(Left(SP(j), 1)) Then b = True
        tmp = Left(SP(j), 1)
    Next j
    If b Then tot = tot + 1
Next i

ALLITERATION = tot
End Function

 

[ollyhughes1982]

Thanks. I don't think that's an option for me in the mac version though. Looking for a way within a formula. Thanks though.

 

[Fluff]

If your only interested in the 1st two words try

=SUM(--IFERROR(LEFT(A2:A10,1)=MID(A2:A10,FIND(" ",A2:A10)+1,1),0))

 

[ollyhughes1982]

If your only interested in the 1st two words try

=SUM(--IFERROR(LEFT(A2:A10,1)=MID(A2:A10,FIND(" ",A2:A10)+1,1),0))

Thanks, that works perfectly for the first two words, so almost there. It could, however, be any two consecutive words within the name.

 

[lrobbo314]

Is Power Query available on Mac?

New__Document (11).xlsx
	A	B	C	D
1	Name		Alliteration Count:	6
2	Bannockburn Bush parkrun
3	Batemans Bay parkrun
4	Benalla Botanical Gardens parkrun
5	Bendigo Botanic Gardens parkrun
6	Cardiff parkrun
7	Newport parkrun
8	Pakapakanthi parkrun
9	Parkville parkrun
10	Sharpham Fiels Road parkrun
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.AddColumn(Source, "Custom", each Text.Split([Name]," ")),
    Left = Table.TransformColumns(Split,{{"Custom", (lst)=> List.Transform(lst, each Text.Upper(Text.Middle(_, 0,1)))}}),
    LG = Table.AddColumn(Left, "Custom.1", (lst)=> 
        let 
            c = List.Count(lst[Custom]),
            g = Number.From(List.Contains(List.Generate(()=> 0, each _ <= c, each _ + 1, each try lst[Custom]{_} = lst[Custom]{_+1} otherwise false),true))
        in
            g
    ),
    Total = List.Sum(LG[Custom.1])
in
    Total

 

[ollyhughes1982]

Is Power Query available on Mac?

New__Document (11).xlsx
	A	B	C	D
1	Name		Alliteration Count:	6
2	Bannockburn Bush parkrun
3	Batemans Bay parkrun
4	Benalla Botanical Gardens parkrun
5	Bendigo Botanic Gardens parkrun
6	Cardiff parkrun
7	Newport parkrun
8	Pakapakanthi parkrun
9	Parkville parkrun
10	Sharpham Fiels Road parkrun
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.AddColumn(Source, "Custom", each Text.Split([Name]," ")),
    Left = Table.TransformColumns(Split,{{"Custom", (lst)=> List.Transform(lst, each Text.Upper(Text.Middle(_, 0,1)))}}),
    LG = Table.AddColumn(Left, "Custom.1", (lst)=>
        let
            c = List.Count(lst[Custom]),
            g = Number.From(List.Contains(List.Generate(()=> 0, each _ <= c, each _ + 1, each try lst[Custom]{_} = lst[Custom]{_+1} otherwise false),true))
        in
            g
    ),
    Total = List.Sum(LG[Custom.1])
in
    Total

No, doesn't appear to be for my version. Thanks again for that though. Complicated / knowledgable stuff!

 

[Fluff]

It could, however, be any two consecutive words within the name

I can currently think of no way to do that.

 

[ollyhughes1982]

I can currently think of no way to do that.

No problem Thanks for trying.

Would this also be the case for repeated words? Another one I have been trying to solve is if a word is repeated at least once in the name. Is it the same case as with letters?

 

[Fluff]

Another one I have been trying to solve is if a word is repeated at least once in the name

That would be simpler, but needs a new thread.