Sequence amount

[deciog]

Gentlemen, good morning.

I have one more problem to solve and I can't find a solution for Excel version 2016 and 365 not VBA explanation in the attached spreadsheet

Thank you very much in advance

Decio

 

[pecoflyer]

Please explain in words what you want to do, without members having to access our sheet

 

[bosco_yip]

In R4, array formula (CSE) copied across and down :

=IFERROR(1/(1/SUM(0+(FREQUENCY(IF($C4:$P4-$B4:$O4=1,COLUMN($C$1:$P$1)),IF($C4:$P4-$B4:$O4<>1,COLUMN($C$1:$P$1)))+1=(1+INT((COLUMN(C$1)-1)/2))))),"")

Edit :

If you wanted to fill blank value in the "Spacer Column", just added a IF checking function in front of the formula and become >>

=IF(R$1="","",IFERROR(1/(1/SUM(0+(FREQUENCY(IF($C4:$P4-$B4:$O4=1,COLUMN($C$1:$P$1)),IF($C4:$P4-$B4:$O4<>1,COLUMN($C$1:$P$1)))+1=(1+INT((COLUMN(C$1)-1)/2))))),""))

 

[deciog]

@bosco_yip, Good morning.

Wow, you're the best, excellent, it worked perfectly.

Thank you so much

A big hug

Decio


@pecoflyer, Good morning.

@bosco_yip has already solved the big problem, thank you too.

Decio

 

[p45cal]

In R4, array formula (CSE) copied across and down

What a brilliant formula! Even taking into account the 'spacer' columns, and extending to sequences of 15 if needed.

I'm playing about with Power Query at the moment and because 'to a hammer, everything is a nail' I've come up with a PQ solution. The table at AE3 in the attached just needs a right-click then a click of Refresh to update. It produces the same results for the entire table, including one 14 sequence and a few other long sequences. This begs a question: @deciog has result columns for only up to 7 sequences, if there is a sequence, say of 10, does he want to see 4 sequences of 7 for that?, or none and one (invisible) sequence of 10?

ps. For this PQ solution to work properly the ID column (left-most) has to be distinct/unique.

 

[deciog]

@ p45cal, Good morning.

I also liked your solution, but I can't put it in the company they don't know how to use Power Query and VBA

Hugs

Decio

 

[p45cal]

but I can't put it in the company they don't know how to use Power Query

What about:

This begs a question: @deciog has result columns for only up to 7 sequences, if there is a sequence, say of 10, does he want to see 4 sequences of 7 for that?, or none and one (invisible) sequence of 10?

Currently, a sequence of 10 doesn't show at all; you have several sequences of 10 at 0058=, 0229= for example, and more.

 

[deciog]

@p45cal

Yes it has several strings, however in this case the player who hits up from 2 to 7 strokes will have a small benefit, I appreciate your help was great

Hugs

Decio

 

[GraH - Guido]

In the like button there should be a "bow" icon for the amazing @bosco_yip. That formula will keep me busy for hours trying to figure out what it does.

 

[Excel Wizard]

Another method
R4

=SUM(--(MMULT(IFERROR(SMALL(IF($C4:$Q4-$B4:$P4<>1,COLUMN($B4:$P4)),ROW($A$1:$A$9)-{1,0}),COLUMNS($B4:$P4)^{0,1}),{-1;1})=(COLUMNS($R4:R4)+1)/2))

Ctrl+Shift+Enter


Custom format 00;; to hide zero

 

[deciog]

Wizard, good afternoon.

The formula is not bringing the expected result, check cell R6 shows 4 the correct is 2, sequence 16.17 and 23.24, for cell AB6 the correct is 1 seq.

The formulas in cell AE4 through A33, is correct, works correctly.

Thank you hug

Decio

 

[Excel Wizard]

Minor change 1 to 3

=SUM(--(MMULT(IFERROR(SMALL(IF($C4:$Q4-$B4:$P4<>1,COLUMN($B4:$P4)),ROW($A$1:$A$9)-{1,0}),COLUMNS($B4:$P4)^{0,1}),{-1;1})=(COLUMNS($R4:R4)+3)/2))

 

[deciog]

@Wizard, Good morning.

Now it is working perfectly.

Thank you very much

Hugs

Decio