Teethless Mama gave me the following formula for my previous question:
=IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) ))
it would be excellent if someone could add the following condition, as my
array skills are scarce:
The list will only include numbers where the first four
numbers coincide with a cell (lets say E5).

Many Thanks!



That was great. But Im not very good at array formulas... complicating my
results. I would like to do two more things with this

1. I want to do this for all the numbers in the list where the first four
numbers coincide with a cell (lets say E5). (Necessary)

2. And I want the result to be just part of the code =MID(data;4;10).
(Optional)

To clarify, the long list has has many subcodes and I want not only the list
of:
4101801
4101901
41011001
41011101
41011205
41011301

but also a list of in another part of the spreadsheet of
44041001
44041101
44041201
44041202
44041301
44041401
44041501

That is why I need the condition.

Many Thanks!


"Teethless mama" wrote:

=IF(ISERR(SMALL(IF(data<"",ROW(INDIRECT("1:"&ROWS (data)))),ROWS($1:1))),"",INDEX(data,SMALL(IF(data <"",ROW(INDIRECT("1:"&ROWS(data)))),ROWS($1:1)) ))

ctrl+shift+enter, not just enter
copy down as far as needed


"Verlaesslichkeit" wrote:

My column looks like this

4101801
4101901
41011001
41011101

there are spaces here

41011205
41011301

Now, I would like to have this list without spaces (without using a filter)
purely by using formulas at another location, so that the final result looks
like this:

4101801
4101901
41011001
41011101
41011205
41011301

I have been trying for hours but dont get tanywhere, Im probably thinking
way to complicated.
Many thanks!