0

I have a sequence of numbers in an Excel worksheet, and I'd like to find the longest subsequence that matches some criteria. For example, say I have the following:

enter image description here

and I'd like to know where the longest subsequence with every value less than 10 starts and how long it is (row 7, 3 long) or greater than equal to 10 (row 4, 3 long), etc.

I can easily generate an array or vector of TRUEs and FALSEs corresponding to the values and whether they meet the criteria I want, but I have no idea even where to start to find and count subsequences.

I'd prefer not to have a VBA solution, simply because I don't really know VBA and if I have to maintain a script, I'll just whip something up in perl that reads the clipboard and writes the result I want back to it.

blm
  • 2,379
  • 2
  • 20
  • 24
  • What is your expected output? – cybernetic.nomad Dec 04 '19 at 20:24
  • @cybernetic.nomad It's in the paragraph right after the sample table, the row number and length of the longest subsequence matching the criteria. – blm Dec 04 '19 at 20:42
  • For those voting to close, it would be nice if you'd comment why. Maybe I can improve the question in some way? – blm Dec 04 '19 at 20:47
  • 1
    I voted to close because there was no attempt made. – BigBen Dec 04 '19 at 20:47
  • @BigBen Thanks for letting me know. As I said, I literally have no idea where to even start. I've spent hours reading through the Excel help and searching here and Google and haven't found anything that gets me anywhere. – blm Dec 04 '19 at 20:50
  • It honestly seems like a non-trivial ask. I'm not sure of a formulaic way to do it (though I am slightly curious to see if someone comes up with one). Without the option to use VBA, I'm not sure how it can be answered. Definitely doable with VBA though, if you change your mind on it. I don't think it'd be that hard. – BigBen Dec 04 '19 at 20:51

1 Answers1

2

Using only formulas you will need a helper column.

  1. in B2, enter the following formula:

    =IF(A2>10,"",IF(A1<=10,B1+1,1))

(this is be your helper column)

  1. In C1, enter:

    =MAX(B:B)

(This is the length of the longest sequence)

  1. Select the entire first column and create a new conditional format with the following formula:

    =AND(ROW()>=MATCH(MAX(B:B),B:B,0)-MAX(B:B)+1,ROW()<=MATCH(MAX(B:B),B:B,0))

You should end up with something like this:

enter image description here

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
  • 1
    Very nice, thank you. One small thing, it doesn't work quite right if the sequence is at the start. Change A1 and A2 to 0, and B1:B3 will be "", 1, 2 and it won't count the first three rows as the longest. If you add `=IF(A1<=10,1,"")` in B1, it fixes it. I'll have to think about that conditional formatting formula :-), but it works nicely. – blm Dec 04 '19 at 22:14