Evaluate whether there are exactly X number of consecutive values in any order

[Dogen]

I am looking for a formula that evaluates whether there are exactly X number of consecutive values in any order (argument 1) including Y number of gaps/missing values (argument 2).

Example 1:
The range is A1:A10
The values are: 53, 52, 51, 1, 3, 4, 2, 21, 8, 7.
Formula argument 1: 4
Formula argument 2: 0

I.e., I want to test whether there are exactly 4 consecutive values in this range in any order, not allowing for gaps or missing values. The formula would return TRUE or 1, since the range contains: 1, 2, 3, 4.

Example 2:
The range is A1:A10
The values are: 53, 52, 51, 1, 3, 4, 2, 21, 8, 5.
Formula argument 1: 4
Formula argument 2: 0

I.e., as above but the formula would return FALSE or 0, since the range contains consecutive series of 3 (51, 52, 53) and 5 (1, 2, 3, 4, 5) but not 4.

Example 3:
The range is A1:A10
The values are: 53, 52, 51, 1, 16, 4, 2, 21, 8, 7.
Formula argument 1: 4
Formula argument 2: 1

I.e., I want to test whether there are exactly 4 consecutive values in this range in any order, including 1 gap or missing value. The formula would return TRUE or 1, since the range contains: 1, 2, GAP, 4 and GAP, 51, 52, 53 and 51, 52, 53, GAP.


I have not been able to produce any formula to do this but instead have to work with a workaround where I do vlookups against a large permutation of possible series which makes operations in the spreadsheet very slow.


Any help or pointers in the right direction is greatly appreciated!

 

[Anthony47]

@Peter_SSs is right when he says that a sequence of 6 with 4 gaps should be marked as TRUE, with his test pattern

And indeed my code should be corrected using For i = -1 To iMax rather than For i = -2 To iMax that I published (we need to test the sequence starting immediately below the initial number).

When testing Peter's CheckSeq, using the his datas in column A and playing with the "Gaps" I see that CheckSeq returns True for a sequence of 7 with 3 Gaps, wereas my corrected CkConsec2 returns False. I could not see a sequence of 7, so I seems that True is wrong.

Anyway Peter's CheckSeq is quite smart, and both my neurons overheated trying to understand the concept

The testing workbook, still downloadable from DemoCkSequence.xlsm now contains my original CkConsec (not corrected as I sayd above), Peter's CheckSeq, and the new CkConsemall c2 that also includes a small code simplification, as follows:

Function CkConsec2(ByRef dRan As Range, ByVal SeqN As Long, _
     Optional ByVal AGap As Long = 0, Optional FGap As Long = 1) As Variant
'https://www.mrexcel.com/board/threads/looking-for-a-formula-that-evaluates-whether-there-are-exactly-x-number-of-consecutive-values-in-any-order.1178589/
Dim oCell, i As Long, mCnt As Long, iMax As Long, cGap As Long
Dim IV As Long, FV As Long, oArr(1 To 3), dBg As Boolean
'
dBg = False
For Each oCell In dRan
''If oCell = 7 Then Stop
    cGap = AGap: IV = 0: FV = 0
    If oCell.Value <> "" Then
        mCnt = 0
        iMax = SeqN - 1
        IV = Application.WorksheetFunction.CountIf(dRan, oCell.Value - 1) * SeqN * 2
        FV = Application.WorksheetFunction.CountIf(dRan, oCell.Value + SeqN) * SeqN * 2
        For i = 0 To iMax
            If Application.WorksheetFunction.CountIf(dRan, oCell.Value + i) > 0 Then
                    mCnt = mCnt + 1
            Else
                If cGap > 0 Then
                    cGap = cGap - 1
                    mCnt = mCnt + 1
                Else
                    If i < iMax Then mCnt = 0
                End If
            End If
        Next i
'Check result for this ocell:
        If (mCnt + IV + FV + cGap * FGap) = SeqN Then
        If dBg Then Debug.Print SeqN, oCell.Value
            oArr(1) = True
            oArr(2) = oCell.Value
            oArr(3) = AGap - cGap
            CkConsec2 = oArr
            Exit Function
        End If
    End If
Next oCell
End Function

Bye

 

[Peter_SSs]

When testing Peter's CheckSeq, using the his datas in column A and playing with the "Gaps" I see that CheckSeq returns True for a sequence of 7 with 3 Gaps, wereas my corrected CkConsec2 returns False. I could not see a sequence of 7, so I seems that True is wrong.

It is hard to get your head around, but wouldn't this satisfy that?
Gap, Gap, 1, 2, 3, Gap ,5

 

[Anthony47]

It is hard to get your head around, but wouldn't this satisfy that?
Gap, Gap, 1, 2, 3, Gap ,5

Let me count... Yes that would make 7!
The problem with my algorithm is that I was not considering Gaps in front of the sequence

Bye

 

[Dogen]

Hmm, perhaps my understanding of the requirement was incorrect.

Below I have Anthony's function in column D. I am wondering about cell D9 not showing TRUE.
By my understanding, for a sequence of 6 with 4 gaps, a suitable sequence from that data would be 7, 8, Gap, Gap, Gap, Gap

Here is my attempt at an alternative function. Function used in column H.

Function CheckSeq(rng As Range, Consec As Long, Optional Gaps As Long = 0) As Boolean
  Dim vals As Variant, itm As Variant, a As Variant
  Dim i As Long, stt As Long, stp As Long
  Dim s As String
 
  If Gaps < Consec Then
    vals = rng.Value
    stt = Application.Min(vals) - Gaps - 2
    stp = Application.Max(vals) + Gaps + 2
    a = Split(Replace(Space(stp - stt + 1), " ", " ,"), ",")
    ReDim Preserve a(stt To stp)
    For Each itm In vals
      a(itm) = "."
    Next itm
    a = Join(a, "")
    For i = 1 To Len(a) - Consec
      s = Mid(a, i, Consec + 2)
      If Left(s, 1) = " " And Right(s, 1) = " " And Len(Replace(s, " ", "")) = Consec - Gaps Then
       CheckSeq = True
       Exit Function
      End If
    Next i
  End If
End Function

Dogen.xlsm
	A	B	C	D	E	F	G	H
1
2	25
3	22		Length	4	<-- Gaps
4	21		1	-				FALSE
5	1		2	-				FALSE
6	3		3	-				FALSE
7	7		4	-				FALSE
8	2		5	TRUE	25	4		TRUE
9	18		6	-				TRUE
10	8		7	TRUE	21	4		TRUE
11	5		8	TRUE	18	4		TRUE
12			9	-				FALSE
13			10	TRUE	1	4		TRUE
14				Result	Matching Value	Gap(s) used
15
Sheet3

Cell Formulas
Range	Formula
D4:D7,D9,D12,D13:F13,D10:F11,D8:F8	D4	=CkConsec($A$2:$A$15,C4,$D$3)
H4:H13	H4	=CheckSeq(A$2:A$11,C4,D$3)

Your interpretation is right and I missed this when testing the UDF. Many thanks for your solution!! (It looks great too).

 

[Peter_SSs]

You're welcome.
Let us know, with examples, if any issues turn up.