Identifying next number in a range and referencing it

V

Verlaesslichkeit

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

Max

This simple, fast play might appeal to you

Source data assumed in A2 down
In B2: =IF(A2="","",ROW())
Leave B1 empty

In C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in col A, eg down
to C1000? Minimize/hide away col B. Col C will return the results that you
seek, all neatly packed at the top
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
 
T

Teethless mama

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

Verlaesslichkeit

I find the other way easier, having not much more formulas to deal with.
Thanks a lot though!
 
V

Verlaesslichkeit

Thanks a lot!

Teethless mama said:
=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
Click to expand...
 
V

Verlaesslichkeit

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

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

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

Max

Any chance that your solution works in xl2003?
If so, could you post a link to your sample in xl2003
This would benefit those w/o the benefit of xl2007
 
V

Verlaesslichkeit

Hi Max! it seems Im coming back to your solution. I would now like to make
this list only when the first four numbers coincide with another cell. Would
really appreciate your help.
 
M

Max

Let's say the 4 digit number is input in D2
(input in D2 is assumed to be a real number)

Just amend the criteria formula in B2 to:
=IF(A2="","",IF(LEFT(A2,4)+0=$D$2,ROW(),""))
Then copy B2 down (no change to the formulae in col C)
and col C will return the desired results neatly packed at the top
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
 
V

Verlaesslichkeit

that would mean I would need 50 different columns (I have 50 different D2
codes) to put the A formula. Is there no way to amend the formula in column C
so that it only makes a list of the numbers starting with criteria in D2?
 
Top