Counting current form

B

BillyBoy

I have a problem I would like solving ,but a I am not sure where to
start. I have 20 rows and 40 columns of three definite results.



1 2 3 4 5 6 7 8
9 10 11 12............40
John J j K j m M m M
k K J J
Martin k K K j M K J j
J k K K
Peter J j J k K K k K
K j M K

I would like to count the highest consecutive run of K or k and the
highest consecutive run of J or j.

I tried the CountIf function but as I said I do not where to start.

Thanks
 
P

Peo Sjoblom

Assuming the codes are in the same row

=MAX(MMULT(--(A5:K5=A13),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13))))
)>=ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))-SIGN(MMULT((ROW(INDIRECT("1:"&C
OUNT(--(A5:K5=A13))))<=(--(A5:K5=A13)=0)*TRANSPOSE(ROW(INDIRECT("1:"&COUNT(-
-(A5:K5=A13))))))+0,(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))>=RO
W(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))+0))))

where A5:K5 is the range you want to text for and A13 holds the letter (in
your case either j or k)
needs to be entered with ctrl + shift & enter
 
B

BillyBoy

Assuming the codes are in the same row

=MAX(MMULT(--(A5:K5=A13),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13))))
)>=ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))-SIGN(MMULT((ROW(INDIRECT("1:"&C
OUNT(--(A5:K5=A13))))<=(--(A5:K5=A13)=0)*TRANSPOSE(ROW(INDIRECT("1:"&COUNT(-
-(A5:K5=A13))))))+0,(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))>=RO
W(INDIRECT("1:"&COUNT(--(A5:K5=A13)))))+0))))

where A5:K5 is the range you want to text for and A13 holds the letter (in
your case either j or k)
needs to be entered with ctrl + shift & enter
Click to expand...
Peo thanks for your response, but when I enter the above formula I
get a message stating that you have typed an error. There is so much
info in the formula correcting is difficult.
Plus can you explain the formula.

Thanks Billy
 
L

Leo Heuser

One more way, with result to look for in A13.

The formula must be entered as one line.

=MAX(LARGE((A5:K5<>A13)*(COLUMN(A5:K5)-COLUMN(A5)+1),COLUMN(A5:J5)-
COLUMN(A5)+1)-LARGE((A5:K5<>A13)*(COLUMN(A5:K5)-COLUMN(A5)+1),COLUMN(B5:K5)-
COLUMN(A5)+1)-1,COLUMNS(A5:K5)-MAX((A5:K5<>A13)*(COLUMN(A5:K5)-
COLUMN(A5)+1)),MIN(IF((A5:K5<>A13),COLUMN(A5:K5)-COLUMN(A5)+1))-1)

Please notice the splits: COLUMN(A5:J5) and COLUMN(B5:K5) instead of
COLUMN(A5:K5)

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter>, also if edited later.
 
Top