Hi Bob: Here the info!
Now I would like to have a list with all numbers starting with 4101 and then
a list with numbers starting with 4102 depending on a value lets say in cell
B5.

4101101
4101201
4101202
4101301
4101401
4101501
4101601
4101701
4101801
4101901
41011001
41011101




41011205
41011301
41011401

4102101


4102203
4102301
4102401

4102502
4102601

Thanks!



"Bob Phillips" wrote:

Then you will need to give us more info, like some sample data, as it worked
in my test.

--
__________________________________
HTH

Bob

"Verlaesslichkeit" wrote in
message ...
Hi Bob! This did not do it. I saw what you did in the formula, I tried
something similar before but it did not work. The result is always "".

"Bob Phillips" wrote:

=IF(ISERR(SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))),"",
INDEX(data,SMALL(IF((data<"")*(--LEFT(data,LEN($E$5))=$E$5),ROW(INDIRECT("1:"&ROWS( data)))),ROWS($1:1))))

--
__________________________________
HTH

Bob

"Verlaesslichkeit" wrote in
message ...
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!