How to get the list where the first number is 2 or 4 from left to right

[rayhen]

When the drop-down menu of cell C1 is selected as 2, the list is as follows (The list is presented below C3)

20201021.jpg


When the drop-down menu of cell C1 is selected as 4, the list is as follows (The list is presented below C3)

20201021-1.jpg

[Pete_UK]

Using column B as a helper, put this formula in B2:

=IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"S",""),"L",""),"H",""))=$C$1&"",MAX(B$1:B1)+1,"")

Copy down to the bottom of your list.

Then you can use this formula in C3:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

and copy this down as far as you need to in order to accommodate the largest sub-list.

Hope this helps.

Pete

[Bo_Ry]

Please try at
D2
=SUM(--(MOD(SMALL(FIND({1,2,3,4,5,6,7,8,9,0},$A$2:$A$25&1/17)*10+{1,2,3,4,5,6,7,8,9,0}+ROW($A$2:$A$25)*10^6,(ROW($A$2:$A$25)-ROW($A$2))*10+1),10)=$C$1))

C3
=IF(ROWS(C$3:C3)>$D$1,"",INDEX(A:A,SMALL(IF(MOD(SMALL(FIND({1,2,3,4,5,6,7,8,9,0},$A$2:$A$25&1/17)*10+{1,2,3,4,5,6,7,8,9,0}+ROW($A$2:$A$25)*10^6,(ROW($A$2:$A$25)-ROW($A$2))*10+1),10)=$C$1,ROW($A$2:$A$25)),ROWS(C$3:C3))))

Ctrl+Shift+Enter

[Pete_UK]

Incidentally, if you are really using Excel 2003, as your profile states, then you won't be able to use the IFERROR function. You will have to use this in C3:

=IF(COUNTIF(B:B,ROWS($1:1)),INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

then copy down.

If you have moved on from version 2003, please update your profile to show this by tapping on the User CP button at the top of the screen.

Pete

[rayhen]

Excellent solution. Thank you both, thank you very much for the solution.

[Pete_UK]

You're welcome - thanks for the rep, and for marking the thread as Solved.

Pete