Conditioning an array formula

[Verlaesslichkeit]

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:
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!

 

[Verlaesslichkeit]

I would like this done without using a pivot table or filter. Many thanks for
your help!

 

[Bob Phillips]

=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))))

 

[Verlaesslichkeit]

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]

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

 

[Verlaesslichkeit]

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!

 

[T. Valko]

Try this:

B5 = 4101

Enter this formula in C5. This will return the number of items that meet the
condition and it will also act as the error trap test cell:

=IF(B5="",0,SUMPRODUCT(--(LEFT(Data,LEN(B5))=B5&"")))

Then, to extract those items use this array formula** entered in D5 and
copied down until you get blanks:

=IF(ROWS(D$55)<=C$5,INDEX(Data,SMALL(IF(LEFT(Data,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$55))-MIN(ROW(Data))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Verlaesslichkeit]

Thats works!

You guys are great. Thanks a lot!

Try this:

B5 = 4101

Enter this formula in C5. This will return the number of items that meet the
condition and it will also act as the error trap test cell:

=IF(B5="",0,SUMPRODUCT(--(LEFT(Data,LEN(B5))=B5&"")))

Then, to extract those items use this array formula** entered in D5 and
copied down until you get blanks:

=IF(ROWS(D$55)<=C$5,INDEX(Data,SMALL(IF(LEFT(Data,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$55))-MIN(ROW(Data))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome. Thanks for the feedback!