checking sequences

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
hi!
how to check sequence in a range c2:h2 ?
i want to check if there's sequence of 4 numbers out of 6, (2-3-4-5-11-20)
and two pairs of sequences out of 6 (11-12-13-20-21-22)
and three sequences pairs of two's (1-2-6-7-22-23)
tried several ways (with one formula with no luck)
if needed for each one different formula, also good
thank you!!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Assuming your six numbers are always sorted, as in your examples, perhaps:

ABCDEFGHIJK
1Sequence?
24+3x22x3Sorted
3TRUEFALSEFALSE123489
4FALSETRUEFALSE123101112
5TRUEFALSEFALSE145678
6FALSEFALSETRUE1210111516
Sheet1
Cell Formulas
RangeFormula
B3:B6B3=ISNUMBER(FIND("111",TEXTJOIN(,,--(G3:K3-F3:J3=1))))
C3:C6C3="11011"=TEXTJOIN(,,--(G3:K3-F3:J3=1))
D3:D6D3="10101"=TEXTJOIN(,,--(G3:K3-F3:J3=1))
 
Upvote 0
Solution
very good!
thank you!

i liked the use of 'patterns' "11011", can you please explain more?
 
Upvote 0
i liked the use of 'patterns' "11011", can you please explain more?
Here's the example of two sequences, length 3:

ABCDEFG
1
2Sorted
3123101112
4
5Differences11711
6Equal to 1?11011
711011
8
Sheet1
Cell Formulas
RangeFormula
C5:G5C5=C3:G3-B3:F3
C6:G6C6=--(C5#=1)
C7C7=TEXTJOIN(,,C6#)
Dynamic array formulas.

The pattern will always be 11011, when you're working with only six numbers.

If you had, say 10 or 20 numbers, and were looking for two sequences of length 3, we'd need to use a different approach.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top