Comparing the same sequence of numbers different from 0

[Luthius]

I have in a row 14 numbers. 0-4.
I would like to indicate "xxx", in case of appear in this line 5 numbers in sequence that are <> of 0

Example:
0 0 0 0 1 1 3 1 1 0 0 0 0 0 "xxx"
0 0 1 0 0 1 2 3 4 0 1 1 0 0
0 1 1 2 1 1 0 0 0 0 1 1 0 0 "xxx"

Each number is on a cell and the result (xxx) in other.
A:N is my range

Luthius

 

[mikerickson]

=IF(0 < SUMPRODUCT(--(COUNTIF(OFFSET(A1:E1,0,COLUMN(A1:J1)-1),"<>0")=5)),"xxx","")

The key part is OFFSET(A1:E1,0,COLUMN(A1:J1)-1)

OFFSET(A1:E1,0,{0,1,2,3,...,9,10}) is an array of the 5 cell ranges A1:E1 - J1:N1

COUNTIF(OFFSET(...),"<>0") is an array of the non-zero count of those ranges.

COUNTIF(...)=5 is an array of TRUE,FALSE as to whether those ranges are all non-0

--(COUNTIF(...)=5) is that array converted to 1, 0

SUMPRODUCT(--(COUNTIF(...)-5)) sums all those 1 and 0

IF(SUMPRODUCT(...)>0, "xxx", """) is the answer

 

[Sektor]

Function HasSequence(Str As String) As String
    Application.Volatile
    HasSequence = IIf(Str Like "*[1-9][1-9][1-9][1-9][1-9]*", "xxx", "")
End Function

 

[Luthius]

mikerickson

Thanks for your explanation, but I'm still afraid of you... Lol...



Sektor, very interesting your UDF. But, is there a way to change your solution in a Excel formula?
I liked your solution for the simplicity, and is there one way to change it to a formula?

Thanks

 

[Sektor]

IT IS FORMULA!!!
Type into cell B1: =HasSequence(A1)
And you'll see result!

 

[Luthius]

No Man, its an UDF. What I mean is native excel formula, in your code the solution was very simple, but I dont want to use VBA to solve the problem, only native formula from Excel.

Thanks

 

[Sektor]

Why not? What prevents you from using UDF?

 

[Luthius]

Man, your solution is very very wonderful. I liked it so much, and I kept it in my library for the future, but as I loved it, I would like to translate your simplicity to an Excel formula.

Thanks

 

[Sektor]

I'm gonna tell you something. Yes, Excel's built-in formulas are much faster than UDFs. It's true. However, if you can't find solution with Excel's formulas, you have to resort to UDF. It's your only solution. If you have not so many such rows to process, better use UDF. Array formulas are good but they are memory-intensive 'cause all array calculations are carried in memory.

 

[Luthius]

I know what you wrote.
...

I was thinking something like

=SEARCH("*[1-9][1-9][1-9][1-9][1-9]*",MYSEQUENCE)

Or

=LOOKUP("*[1-9][1-9][1-9][1-9][1-9]*",A6:N6)

Use your criteria to get result.

What you think?

 

[Sektor]

It's useless 'cause you have several ranges with non-zero values like: 001234000110098756000.
The problem is that first non-zero range has four digits. Going further we get second range with two digits. Finally, we have third range which has five digits. Counting of non-zero digits is silly. Use UDF and forget it!