View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernd P Bernd P is offline
external usenet poster
 
Posts: 806
Default Numbers of repeats of a string

Hello Luciano,

That was left as homework exercise :-)

Here we a
Function ShowRepetitions(v As Variant) As Variant
Dim i As Long, j As Long

On Error Resume Next
Do While v(i, 2) 1
i = i + 1
Loop
If v(i, 2) = 1 Then i = i - 1
On Error GoTo 0

ReDim vR(1 To Application.Caller.Rows.Count, 1 To 2) As Variant

For j = 1 To Application.Caller.Rows.Count
If j i Then
vR(j, 1) = ""
vR(j, 2) = ""
Else
vR(j, 1) = v(j, 1)
vR(j, 2) = v(j, 2)
End If
Next j

ShowRepetitions = vR

End Function

Array-enter:
=showrepetitions(GSort(pfreq(TRANSPOSE(GenSubStrin gs
(A1))),"DA","NS","21"))

Regards,
Bernd