Calc - Count a "similar sequence"

classic Classic list List threaded Threaded
4 messages Options
cje cje
Reply | Threaded
Open this post in threaded view
|

Calc - Count a "similar sequence"

Is there a way to count a sequence of data as long as they are similar?
We have a datasheet were the sales of our sales men are imported.
One of the information we’re using is counting for how long one sales man
has been on top.

Ex.:
Sales man#1
Sales man#1
Sales man#1
Sales man#2
Sales man#1
Sales man#3

Is here a way that calc can detect that the data is changing and stop the
counting?
The above sample should return 3

Looking forward to any suggestion.



--
View this message in context: http://nabble.documentfoundation.org/Calc-Count-a-similar-sequence-tp4185391.html
Sent from the Users mailing list archive at Nabble.com.

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
hsikkema hsikkema
Reply | Threaded
Open this post in threaded view
|

Re: Calc - Count a "similar sequence"

I often do counting in a secondary column.

See the attached file for a possible solution:
length_of_longest_run.ods
Piet van Oostrum-2 Piet van Oostrum-2
Reply | Threaded
Open this post in threaded view
|

Re: Calc - Count a "similar sequence"

In reply to this post by cje
cje wrote:

 > Is there a way to count a sequence of data as long as they are similar?
 > We have a datasheet were the sales of our sales men are imported.
 > One of the information we’re using is counting for how long one sales man
 > has been on top.
 >
 > Ex.:
 > Sales man#1
 > Sales man#1
 > Sales man#1
 > Sales man#2
 > Sales man#1
 > Sales man#3
 >
 > Is here a way that calc can detect that the data is changing and stop the
 > counting?
 > The above sample should return 3
 >
 > Looking forward to any suggestion.

You can write a macro for that, e.g. in Basic.

Here is one for the above problem:

------------------------------------------------------------------------
Function CountEquals(r())
        ' r must be a one row or one column range
       
        If Lbound(r,1) = Ubound(r,1) Then
                idx = 2
        Else
                idx = 1
        End If
       
        num = 0
        idx1 = Lbound(r,idx)
        idx2 = Lbound(r,3-idx)
        first = r(idx1,idx2)
        While idx1 <= Ubound(r,1) and idx2 <= Ubound(r,2)
                If r(idx1,idx2) = first Then
                        num = num + 1
                End If
                If idx = 1 Then
                        idx1 = idx1 + 1
                Else
                        idx2 = idx2 + 1
                End If
        Wend

        CountEquals = num
       
End Function
------------------------------------------------------------------------

Now you can use in a cell, e.g. =CountEquals(A5:A10)
--
Piet van Oostrum <[hidden email]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Mike Scott-2 Mike Scott-2
Reply | Threaded
Open this post in threaded view
|

Re: Calc - Count a "similar sequence"

In reply to this post by hsikkema
[resent; for some reason the first copy's not appeared on the list.
Apologies if it appears twice.]

On 06/06/16 16:06, hsikkema wrote:
> I often do counting in a secondary column.
>
> See the attached file for a possible solution:
> length_of_longest_run.ods
> <http://nabble.documentfoundation.org/file/n4185405/length_of_longest_run.ods>
>

A lot depends on the OP's needs.

If it's a once in a blue moon computation, there's a lot to said for
keeping things simple: export the salesman name column to a flat text
file, and run it through 'uniq -c' (OK, 'nix-specific, but nevertheless
available for windows).

But the OP did actually specify "similar", not "identical", in which
case one is entering very murky waters, and a precise definition of
'similar' is needed.


--
Mike Scott
Harlow, Essex
"The only way is Brexit" -- anon.

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted