Conditioning an array formula
 

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!

Conditioning an array formula
 

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

"Verlaesslichkeit" wrote:

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!

Conditioning an array formula
 

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

Conditioning an array formula
 

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!

Conditioning an array formula
 

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!

Conditioning an array formula
 

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!

Conditioning an array formula
 

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$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"")

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

--
Biff
Microsoft Excel MVP


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

Conditioning an array formula
 

Thats works!

You guys are great. Thanks a lot!

"T. Valko" wrote:

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$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"")

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

--
Biff
Microsoft Excel MVP


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

Conditioning an array formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Verlaesslichkeit" wrote in
message ...
Thats works!

You guys are great. Thanks a lot!

"T. Valko" wrote:

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$5:D5)<=C$5,INDEX(Data,SMALL(IF(LEFT(Dat a,LEN(B$5))=B$5&"",ROW(Data)),ROWS(D$5:D5))-MIN(ROW(Data))+1),"")

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

--
Biff
Microsoft Excel MVP


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