Identifying next number in a range and referencing it

  • Thread starter Thread starter Verlaesslichkeit
  • Start date Start date
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!
 
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
 
=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
 
I find the other way easier, having not much more formulas to deal with.
Thanks a lot though!
 
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...
 
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!
 
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
 
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.
 
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
 
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?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
Referencing A Non Consecutive Range? 3
Concat a three column range into one cell Then Next... 2
Creating A List of Items Between Two Numbers 2
Dynamic display 1
Post my named ranges in column A 10
Convert a phone number 3
SUMIF using date ranges & referencing account numbers 1

Back
Top