Count the ordered frequency/sequence of 0 and 1

[Sumsar93]

Hi,

I need help to count the Frequency (sequence) of 0 and 1 in a row. My data looks like this (e.g):

1, 0, 0, 1, 0, 0, 0, 1, 1, 1.

and I want the output to be:

1, 2, 1, 3, 3.

another example:

1, 1, 1, 0, 0, 0,

and I here want the output to be:

3, 3,

Many thanks! and hope I made myself clear

Note: If it´s possible to show which frequency/sequence number that belongs to 0 or 1 is a bonus, but not necessary.

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[Sumsar93]

Thanks,

I no attached a file to the question.

[alansidman]

Excel says file is corrupted. Would not open

[Sumsar93]

Sorry for that now it should work.

[Bo_Ry]

Please try
=TRANSPOSE(FILTER(FREQUENCY(COLUMN(G10:R10),COLUMN(G10:R10)/(G10:R10<>H10:S10)),FREQUENCY(COLUMN(G10:R10),COLUMN(G10:R10)/(G10:R10<>H10:S10))))

or with LET
=LET(a,FREQUENCY(COLUMN(G10:R10),COLUMN(G10:R10)/(G10:R10<>H10:S10)),TRANSPOSE(FILTER(a,a)))

[Sumsar93]

Hi, I tried to use the formulas but it wont work, I attach an example. It says the formula uses unrecognised text.

Best regards,

rasmus

[kvsrinivasamurthy]

code for UDF FrequncyArray

Please Login or Register  to view this content.

[Sumsar93]

Thank you very much, It worked perfect! Is it possible to get the ouput in separate cells?

[kvsrinivasamurthy]

If result is required in different cells instead of Function macro is required. Are you ok with it.
code

Please Login or Register  to view this content.

[Sumsar93]

Okay, then it´s fine. Thank you!

[Sumsar93]

Hi again,

The freq array seems to give the wrong output when the sequence ends with 2 unequal numbers (010, 101) plz, see the attached example file.

Would really appreciate help with this, since my calculations rely on this to work.

Regards,

Rasmus Johansson

[kvsrinivasamurthy]

Revised code

Please Login or Register  to view this content.

[Sumsar93]

thanks works perfectly now!

[Bo_Ry]

Try

G3
=TEXTJOIN(",",,TEXT(FREQUENCY(COLUMN(C3:E3),IF(C3:D3<>D3:E3,COLUMN(C3:D3))),"0;;"))

E18
=TEXTJOIN(",",,TEXT(FREQUENCY(COLUMN(G18:M18),IF(G18:L18<>H18:M18,COLUMN(G18:L18))),"0;;"))

or UDF

Please Login or Register  to view this content.