You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"INTP56" wrote in message
...
Rag,

At first, I didn't think this was going to work, but after I paid more
attention I realized this was a very clever way to get what I wanted.

Thanks again, another trick for my toolbag!

Bob

"RagDyer" wrote:

Sorry ... WRONG formulas!

Use these:

For start in A1:
=MIN(IF(A3:A500<A3,ROW(3:500)))

For end in A2:
=MAX(IF(A3:A500<A500,ROW(3:500)))

Still *array* formulas.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
With imported data starting in A3, try this *array* formula in A1 for
the
*ROW* number of the start of data:

=MIN(IF(A1:A500<A1,ROW(1:500)))

and in A2 for the *ROW* number of the end of data:

=MAX(IF(A1:A500<A500,ROW(1:500)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------


"INTP56" wrote in message
...
Thanks Peo, this is exactly the kind of thing I was looking for.

This won't "look up" the array, but just being able to know where I
start
is
great.

Thanks again,

Bob

"Peo Sjoblom" wrote:

=INDEX(A2:A30000,MATCH(TRUE,A2:A30000<A2,0))

entered with ctrl + shift & enter


will return the contents of the first row in A2:A3000 not equal to A2



--


Regards,


Peo Sjoblom





"INTP56" wrote in message
...
Excel 2003

I often get data files from instruments where many initial rows all
have
the
same value, typically zero, but not limited to that. Often these
files
are
text files with 100+ columns.

For example, I might have a file with 30,000 rows in it, but some
columns
don't start having data till row 20,000 or so.

What I would like is a worksheet function where I could give it a
range,
and
it return the first cell (Index or value) that is not equal to the
value
in
the first data row.

I'd like to do this without creating extra columns, or having to
manually
iterate through thousands of cells for 100+ columns. Functionally, I
would
type a formula into a cell above the first column in question, then
drag
it
across all the other columns so each column would have it's own
individual
value for the first different value.

The icing on the cake would be to do the same thing, except this
time
look
from the bottom up.

Is this possible?

Thanks, Bob