Filter the names with consecutive numbers greater than or equal to 2

[petter_hoang]

Please help me with the formula at E3:E5


Thanks very much

 

[GraH - Guido]

Nice challenge to test out some modern Excel functions.

However I got stuck in the let function, where I can't manage to get BYROW ( ) work.

So far:
[D3]:
=LET(_dNames;UNIQUE(data[NAME]);
_Cnt_names;COUNTIF(data[NAME];_dNames);
_min;MINIFS(data[NUMBER];data[NAME];_dNames);
_Stack;HSTACK(_dNames;_Cnt_names;_min);
_flt;FILTER(_Stack;_Cnt_names>2);
_flt)

Fills the range D3:F3 and provides me the details to create a sequence to which we can match the numbers.

[G3] (filled down): =SUM(--(SEQUENCE(;E3;F3;1)=AGGREGATE(15;6;data[NUMBER]/(data[NAME]=D3);SEQUENCE(;E3))))=E3
checks the condition that the numbers are in sequence, starting from the minimum value of number for each name. The sum of TRUEs must match
the number of times the name occurs (countif).
It is this part I cannot figure out to make it work with a BYROW/LAMBDA combo.

[I:3]: =FILTER(D3:D500;G3:G500) delivers he final result.



Looking forward to the replies of some of our formula wizards.

 

[bosco_yip]

Try this helper formula solution, for your Excel 2010

In Helper C3, formula copied down:

=IF(B3-1=B2,"",IF(MATCH(TRUE,INDEX(B3:B18+1<>B4:B19,0),0)>1,MAX(C$2:C2)+1,""))

In Result E3, formula copied down:

=IFERROR(INDEX(A$3:A$19,MATCH(ROW(A1),C$3:C$19,0)),"")

 

[shrivallabha]

If you have access to UNIQUE and FILTER functions then you can try (might be CTRL+SHIFT+ENTER depending on your version of Excel):
=UNIQUE(FILTER(A3:A19,IF(A3:A19=A4:A20,IF(B3:B19>=2,IF(B3:B19=(B4:B20-1),TRUE,FALSE),FALSE),FALSE)))

 

[GraH - Guido]

To understand the "ins" of the formulae provided by both Ninjas, I changed 1 number in B15. Still the sequence of "H" is broken. However the title being "Filter the names with consecutive numbers greater than or equal to 2", means my futile attempt fails.
The cunning thing in both solutions is referring to the same column range, yet offset by 1 cell (be it up or down). That's a practise that isn't in my trickery box.

 

[p45cal]

Would A still be included if:


and how about:


and finally, would C be included in these circumstances?:

 

[bosco_yip]

Would A still be included if:
View attachment 81656

and how about:
View attachment 81657

and finally, would C be included in these circumstances?:
View attachment 81658

Hi p45cal,

What are Consecutive Numbers?
Numbers that follow each other continuously in the order from smallest to largest are called consecutive numbers.

For example:

1, 2, 3, 4, 5, 6, and so on are consecutive numbers.

So,

Your examples are not related to the OP's question.

Regards