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!

 

[Peter_SSs]

Welcome to the MrExcel board!

Happy to be proven wrong, but I doubt a worksheet formula solution will be feasible. Would a vba solution be acceptable if that can be constructed?

Also, to clarify, consider this example

The values are: 53, 52, 51, 12, 16, 4, 28, 21, 8, 40.
Formula argument 1: 3
Formula argument 2: 1

Although Gaps is set to 1, is this still TRUE because 51, 52, 53 exists, or does a gap have to be included as part of the sequence of 3?

 

[Dogen]

Thanks for the welcome!

Yes, absolutely happy with a vba solution. It's probably preferable. I tried with vba first but got nowhere unfortunately.. Been running circles around this for a while..

The gaps/missing values are "mandatory" so in this example the formula would return FALSE - the gaps have to be included.

 

[Peter_SSs]

I did have an idea that I thought might work but further testing proved that incorrect and I can't seem to find a logical way. The 'gaps', especially ones that might come at the beginning or end of the sequence, are making it hard, as does the requirement to have exactly the right length sequence and not any longer.
I will keep the issue in the back of my mind and if further ideas crop up I will come back and test them.

 

[Snakehips]

@Dogen Can you please clarify.
Are you wanting to apply this to just row A ?
Always 10 data values?
Indication of max value for argument 1 ?
Indication of max value for argument 2 ?

 

[Snakehips]

^^^ COLUMN A

 

[Anthony47]

I risked overheating my left neuron, but got the following UDF working, maybe working according @Dogen requirements:

Function CkConsec(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
    cGap = AGap: IV = 0: FV = 0
    If oCell.Value <> "" Then
        mCnt = 0
        iMax = SeqN
        For I = -2 To iMax
            If Application.WorksheetFunction.CountIf(dRan, oCell.Value + I) > 0 Then
                If I < 0 Then
                    IV = 1
                ElseIf I = iMax Then
                    FV = 1
                Else
                    mCnt = mCnt + 1
                End If
            Else
                If cGap > 0 And I >= 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
            CkConsec = oArr
            Exit Function
        End If
    End If
Next oCell
End Function

Copy the code into a Standard vba Module, then you can write a formula like this:

=CkConsec(TheAddressOfTheDatas, TheLengthOfTheSequence [optional: TheNumberOfAllowedGaps])

For example:

=CkConsec($A$2:$A$15,4)        'a sequence of 4 (0 gaps)
=CkConsec($A$2:$A$15,C4)       'a sequence of what is in C4  (0 gaps)
=CkConsec($A$2:$A$15,5,2)      'a sequenceof 5 with 2 gaps

The number of GAPS can be omittend (defaulted to Zero)

At the moment the function returns 3 values:
-true /false (the basic result)
-which number geated the result
-how many Gaps were inserted
The second and third output are for debugging purpose only, thus could be removed.

If you use Office 365 (or a version that supports Dynamic arrays) the result will automatically span horizontal to fill 3 columns (unless the #Expansion error be generated, if the columns are already busy); in other versions, to get the 3 results you have to
-select horizontally 3 cells; type the formula in the Formula bar; confirm it using the keys Contr+Shift+Enter (Enter alone will fill only the True/false)

A demo workbook is available here: DemoCkSequence.xlsm
Formulas are in D4:D13; the Length is in C4:C13; the number of gaps is in D3; the serie of data is in A2:A15 (empty cells are ignored).
The formula used is =CkConsec($A$2:$A$15;C4;$D$3) (in D4, then copied up to D13)
This is compiled using Office 365, it is possible you have to re-enter the formulas

Hope this helps

Bye

 

[Dogen]

It works!! I bow to your left neuron!

I would have never have come up with this solution myself - tons of thanks, this is amazing. I will study the code in more detail, hopefully I'll learn something from this too.

 

[Dogen]

And thanks to anyone else who gave this thought and attention!

 

[Peter_SSs]

It works!!

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)