count the same values

[ABBOV]

Hello,

I.ve got a small problem, maybe someone can help.

In a column i've got the values shown beneath.
Col AM
ROW 1 xxxxxxxcxxcxccxcccxxxxxxx
ROW 2 xcccccxccccxccccxcxcccccx
ROW 3 xcxxxcxcxxccxcxxccxcxxxcx

I''m looking for a formula that counts the value "x"until the value "c".

So,
A1, counts the first set of "x" .
B1, counts the second set of "x"
C1, 3th set of "x"
this would be repeated with max 10 colums

The outcome will be something like this:

Column A B C D E F G H
ROW 1 ) 7 2 1 1 7
ROW 2 ) 1 1 1 1 1 1
ROW 3 ) 3 1 2 1 2 1 3 1


Anyone an idea?

Thanks in advance.

ABBOV

[DonkeyOte]

Based on your sample - using the rows - I'd be inclined to keep a count of the number of items to be retrieved:

AO12:
=SUMPRODUCT(--(MID(AM12&REPT("c",RIGHT(AM12)="x"),ROW($1:$25),2)="xc"))
copied down to AO36

With the above in place - in terms of replicating your expected results

C12:
=IF((9-COLUMNS($C12:C12))>$AO12,"",($L12="x")+FIND("^^",SUBSTITUTE($AM12&REPT("c",$AJ12="x"),"xc","^^",1+COUNT($B12:B12)))-FIND("^^",SUBSTITUTE(REPT("c",$L12="x")&$AM12,"cx","^^",1+COUNT($B12:B12))))
copied across matrix C12:J36

note there is an error in your sample - row 23 should be 2 2 4 1 2 rather than 2 2 1 4 1 2

[Marcol]

Here's a method using various formulae, paste special, transpose and text to columns.

You might be able to do a better job with the vertical values.

[ABBOV]

Here's a method using various formulae, paste special, transpose and text to columns.

You might be able to do a better job with the vertical values.

Thanks Marcol,

That does the trick.

[Marcol]

DonkeyOte

Should your formula not allow for 13 values per row?

I found 9 values in row 33.

[DonkeyOte]

Marcol, I don't understand the question

I was just looking to ape the expected results in the sample provided - ie capped at 7 "streaks" (C:J) per row
(there are 10 in row 33)

edit: I see A:B do store values after all... I didn't bother to check .. my bad.

[Marcol]

DonkeyOte

My logic is if alternating cells are black then there will be a maximum of 13 "x" values

[DonkeyOte]

There are only 10 columns available.

J36:
=IF((11-COLUMNS($A36:J36))>$AO36,"",($L36="x")+FIND("^^",SUBSTITUTE($AM36&REPT("c",$AJ36="x"),"xc","^^",1+COUNT($A36:I36)))-FIND("^^",SUBSTITUTE(REPT("c",$L36="x")&$AM36,"cx","^^",1+COUNT($A36:I36))))
copied up and over matrix A12:J36
(reason for copying right to left being the dynamic COUNT range and Col A)

[Marcol]

The real trick is to develope DonkeyOtes' formulae to cover all eventualities.

I'm not a whizz-kid with formulae, so I posted a step-by-step procedure.

Developing Dons' solution would give you a "live" solution that would update as you changed the values in your grid, something my solution fails to do.

[DonkeyOte]

In truth I think you should go for whatever route makes sense and you prefer

FWIW - I appreciate my approach might not be the easiest to implement given ranges etc so attached is my version.

I have added the vertical section also - though like the horizontal results I have 1 discrepancy which is that in Col M the result should be 8 rather than 9 1's (expected results implied 9)

As per the sample file the first 10 frequencies are returned - should you have more then they are not reported (this is per the setup of your file).