4

Please help me to find sequence of numbers present in column of a spreadsheet.

I have a large data of 1's and 0's in an excel column, I need to find the sequence of consecutive 1's and 0's in the column. For example, my excel column is given below:

0
0
1
1
0
0
1
0
1
0
0
1
1
0
1
1
1
0
0

Please assume this as some of my column data, I need to find where are the sequences of 1's and 0's present in the column. For example, I need to find:

0
0
1
1
0
1

This sequence of data in the column. Is there any function for sequence search or something? Please Help!!

JvdV
  • 70,606
  • 8
  • 39
  • 70
Aashiq Shajahan
  • 55
  • 5

2 Answers2

1

In Excel 2019 and O365, it's rather easy using CONCAT():

=FIND("001101",CONCAT(A1:A19))

However, combining a range of cells prior to these versions of Excel can be tricky. You will quickly get tangled up in syntax like =MAX((A1:A19=0)*(A2:A20=0)*(A3:A21=1)*(A4:A22=1)*(A5:A23=0)*(A6:A24=1)*(ROW(A1:A19))) (as an array formula). An UDF is not a bad alternative in this case:

Function FINDSEQ(seq As String, rng as Range) As Long
    FINDSEQ = InStr(1, Join(Application.Transpose(rng.Value), ""), seq)
End Function

Call like: =FINDSEQ("001101",A1:A19)

Note - Better solutions may be found since Application.Transpose will have an upperbound limit.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Unfortunately =FINDSEQ function is not available in my Microsoft Excel 2016 version. – Aashiq Shajahan Jul 27 '20 at 07:22
  • @AashiqShajahan, it's a custom function. Not available to anyone unless the above code is pasted in a module. See [this](https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f) – JvdV Jul 27 '20 at 07:26
  • Thank you. It worked. Can I find if there are multiple instances of these sequence of numbers in the column? – Aashiq Shajahan Jul 27 '20 at 07:33
  • Sure, looks like an appropriate new question to me as soon as you have given it a try yourself. You are welcome to open new questions. – JvdV Jul 27 '20 at 07:37
  • Could you please answer it here itself. Please..... Shall I edit this question?? – Aashiq Shajahan Jul 27 '20 at 07:38
  • Never change the question so substantially that it would invalidate existing answers. If the OP is answered you are welcome to open new questions. – JvdV Jul 27 '20 at 07:42
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218665/discussion-between-aashiq-shajahan-and-jvdv). – Aashiq Shajahan Jul 27 '20 at 07:43
0

If the numbers are formated as string you can simply do: =search("1 1 0 1 0 1";A1)

Andreas
  • 8,694
  • 3
  • 14
  • 38
  • There is an error in the format of the question, Actually the 1's and 0's data I typed was as line by line but it was automatically corrected like this. That is I assumed that data inside a column. – Aashiq Shajahan Jul 27 '20 at 06:27
  • Formula will be better for me, because I am not much used to VBA. – Aashiq Shajahan Jul 27 '20 at 06:39