Posted to microsoft.public.excel.misc
|
|
Identifying next number in a range and referencing it
I find the other way easier, having not much more formulas to deal with.
Thanks a lot though!
"Max" wrote:
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,R OWS($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
---
"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!
|