TRICKY Qs - Check if a series is missing in given range

[adiritz]

In Column A, I have numbers from 1 to 40.

From cell B16 and so on, I want to display a series of 9 continuous numbers which did not appear in last 15 rows(range A1:A15)

COLUMN A

1
12
24
40
35
30
33
2
25
17
8
0
20
39
30

DISPLAY "NONE" as no continuous 9 numbers were missing in the last 15 rows


1
12
24
40
35
30
33
2
25
17
26
0
20
39
30

DISPLAY "3-11" as continuous 9 numbers from 3 to 11 were missing in the last 15 rows

Is this possible?

Please advice.

 

[Marcelo Branco]

Maybe something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Numbers​

2​

1​

3​

12​

4​

24​

5​

40​

6​

35​

7​

30​

8​

33​

9​

2​

10​

25​

11​

17​

12​

8​

13​

0​

14​

20​

15​

39​

MaxMissing​

Largest​

List​

16​

30​

5​

N/A​

17​

1​

18​

12​

19​

24​

20​

40​

21​

35​

22​

30​

23​

33​

24​

2​

25​

25​

26​

17​

27​

26​

28​

0​

29​

20​

30​

39​

MaxMissing​

Largest​

List​

31​

30​

9​

11​

3​

4​

5​

6​

7​

8​

9​

10​

11​

<tbody>
</tbody>

Array formula in B16
=MAX(FREQUENCY(IF(ISNA(MATCH(ROW($1:$41)-1,A2:A16,0)),ROW($1:$41)-1),A2:A16))
Ctrl+Shift+Enter

Array formula in C16
=IF(B16>=9,INDEX(A2:A16,MATCH(B16,FREQUENCY(IF(ISNA(MATCH(ROW($1:$41)-1,A2:A16,0)),ROW($1:$41)-1),A2:A16),0))-1,"N/A")
Ctrl+Shift+Enter

Regular formula in E16 copied across as long as needed (till N16 for example)
=IF($B16>=9,IF($B16>=COLUMNS($E16:E16),$C16-$B16+COLUMNS($E2:E2),""),"")
confirmed with just Enter

Then
select cells B15:N16 and copy (Ctrl+C)
select cell B30 and paste (Ctrl+V)

Done!

Hope this helps

M.

 

[Marcelo Branco]

oops... the formula in E16 has a typo (not important for calculation)

New version
=IF($B16>=9,IF($B16>=COLUMNS($E16:E16),$C16-$B16+COLUMNS($E16:E16),""),"")

M.

 

[adiritz]

oops... the formula in E16 has a typo (not important for calculation)

New version
=IF($B16>=9,IF($B16>=COLUMNS($E16:E16),$C16-$B16+COLUMNS($E16:E16),""),"")

M.

Thank you so much.

It worked perfectly.

I have posted one more Qs

Check which value appeared three times in a range

Is it possible to help me in that, please.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.